I am new to SQL and struck with this below scenario, can any one of you please help on this.
I want to check if the ingredients of a product allowed on production line if so then production line names for the product where it can be produced.
Table 1
ProductionLine Allergen
BB1 Tree nut
BB1 Peanut
BB1 Milk
BB1 Wheat
BB2 Tree nut
BB2 Peanut
BB2 Milk
BB2 soy
BB2 Egg
Table 2
Product Ingredients
P1 Tree nut
P1 Peanut
P1 Milk
P1 soy
Here the Product P1 can be produced on BB2 line as all the ingredients are allowed on BB2 Allergen list. so i want to have the result set as
Expected Result
Product Ingredients ProductionLine
P1 Tree nut BB2
P1 Peanut BB2
P1 Milk BB2
P1 soy BB2
If any one of the ingredient is not allowed on any line then we can not produce product on that line.
Assuming there are no duplicates in either table, you can use a left join
and group by
. The key is counting the number of matches and being sure that it matches the total number of ingredients.
select t2.product, t1.productionline
from (select t2.*,
count(*) over (partition by product) as num_products
from table2 t2
) t2 left join
table1 t1
on t2.ingredient = t1.allergen
group by t2.product, t1.productionline, t2.num_products
having count(t1.ingredient) = num_products;