Select from multiple tables with group and join

i have 4 tabels

drinks, opskrifter, ingredients and stock

the tables consist of

drinks

• drink_id
• name
• beskriv
• glas
• image
• alcohol

opskrifter

• drink_id
• ingredient_id
• quantity

ingredients

• ingredient_id
• name

stock

• ingredient_id
• name

i want a query to select drinks that can be made in opskrifter of the ingredients in stock.

i have this working, but it only returns drink_id.

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) 

I want it to return:

drink_id, name, beskriv, glas, image, alcohol

somebody help my on the way 🙂 thx

Answer

Basically you need to bring the drink table. I would just use a join for that, while turning the aggregate query to a subquery:

select d.*, o.cnt_ingredients
from drink d
inner join (
    select o.drink_id, count(*) as cnt_ingredients
    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) 
) o on o.drink_id = d.drink_id