Return only recipes that contains all ingredients Sql

Question

I have these 3 tables:

Drinks

• drink_id
• name

Ingredients

• ingredient_id
• name

Opskrifter

• drink_id
• ingredient_id
• quantity

Drinks and Ingredients are cross-referenced in opskrifter.

I want to return all recipes from opskrifter that have ingredients from another table called

Stock

• name

So to make a gin and tonic, I need to have both gin and tonic in stock.

If I only have coke and tonic, I should return nothing

This is what I've got so far, copy/pasted from other post, but can't get any further.

Select
    d.*
From
    drinks d
Where
    not exists (select 1 
                from opskrifter r 
                where r.drink_id = d.drink_id 
                  and r.ingredient_id in (1, 2, 3))

Please help :-)

Answer 1

I want to return all recipies from opskrifter that has ingredients from another table called stock.

I understand that you want drinks that have all ingredients that are listed in the stock table. Assuming that you have a column called ingredient_id in stock, you could phrase this as:

select o.drink_id
from opskrifter o
inner join stock s on s.ingredient_id = o.ingredient_id
group by o.drink_id
having count(*) = (select count(*) from stock)

Alternatively, if you want drinks whose all ingredients are available in stock:

select o.drink_id
from opskrifter o
left join stock s on s.ingredient_id = o.ingredient_id
group by o.drink_id
having count(*) = count(s.ingredient_id)