Skip to content
Advertisement

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

Advertisement

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 
Advertisement