kobim kobim - 27 days ago 7
MySQL Question

MySQL SELECT query with a many to many relationship

I'm having trouble making a SELECT/WHERE query using a many to many relationship type. A user inputs ingredients, and I want to find which recipes use all the ingredients provided among the other ingredients (if any). (Think: use up the last ingredients I have in my fridge)

My DB is currently designed like this:
database structure

recipes_ingredients
looks like this
recipes ingredients

For example, if I give,
id_ingredient IN (22, 23)
i want the recipe #16497 only, not #16631 (since it only has 22 and not 23).

I've come up with something that does the opposite of what I described

SELECT DISTINCT recipes.*
FROM recipes_ingredients
JOIN recipes ON recipes_ingredients.id_recipe = recipes.id
WHERE id_ingredient IN ( 96, 13196 )

Answer Source

If you want to get recipes which should have these both ingredients(not single ingredient) then you can use aggregation with some filter

SELECT  r.*
FROM recipes_ingredients i
JOIN recipes r ON i.id_recipe = r.id
WHERE i.id_ingredient IN ( 96, 13196 )
GROUP BY r.id
HAVING COUNT(DISTINCT i.id_ingredient ) = 2

OR

SELECT  r.*
FROM recipes_ingredients i
JOIN recipes r ON i.id_recipe = r.id
GROUP BY r.id
HAVING SUM(i.id_ingredient = 96)
AND SUM(i.id_ingredient = 13196)