By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE product (
id int,
name varchar(25)
);
insert into product values
(1, 'A'),
(2, 'B');
CREATE TABLE Product_A_Type (
id int,
type varchar(25)
);
insert into Product_A_Type values
(1, 'abc'),
(2, 'dcf');
CREATE TABLE Product_B_Type (
id int,
type varchar(25)
);
insert into Product_B_Type values
(1, '123'),
(2, '456');
CREATE TABLE daily_stock (
id int,
product_id int,
product_type_id int
);
insert into daily_stock values
(1,1,1),
Records: 2 Duplicates: 0 Warnings: 0
Records: 2 Duplicates: 0 Warnings: 0
Records: 2 Duplicates: 0 Warnings: 0
Records: 4 Duplicates: 0 Warnings: 0
select *
from daily_stock ds
inner join product p on p.id = ds.product_id
inner join Product_A_Type pa on pa.id = ds.product_type_id
inner join Product_B_Type pb on pb.id = ds.product_type_id
| id | product_id | product_type_id | id | name | id | type | id | type |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | A | 1 | abc | 1 | 123 |
| 3 | 2 | 1 | 2 | B | 1 | abc | 1 | 123 |
| 2 | 1 | 2 | 1 | A | 2 | dcf | 2 | 456 |
| 4 | 2 | 2 | 2 | B | 2 | dcf | 2 | 456 |
select ds.id, ds.product_id, ds.product_type_id
from daily_stock ds
inner join product p on p.id = ds.product_id
inner join Product_A_Type pa on pa.id = ds.product_type_id
inner join Product_B_Type pb on pb.id = ds.product_type_id
where p.name = 'A' and ( pa.type like '%ab%' or pb.type like '%ab%' )
| id | product_id | product_type_id |
|---|---|---|
| 1 | 1 | 1 |
select ds.id, ds.product_id, ds.product_type_id
from daily_stock ds
inner join product p on p.id = ds.product_id
inner join Product_A_Type pa on pa.id = ds.product_type_id
inner join Product_B_Type pb on pb.id = ds.product_type_id
where p.name = 'B' and ( pb.type like '%12%' or pb.type like '%12%' )
| id | product_id | product_type_id |
|---|---|---|
| 3 | 2 | 1 |