SQL query "ContainsAll"

Question

I have a recipe table and an ingredient table and a table that connect the ingredients to the recipes.

I have a list of ingredients, how do I write a SELECT statement (or a storage procedure) that will return recipes that have ALL of the ingredients presented?

How to write this query for MySQL?

Answer 1

I think I finally have a solution. :)

SELECT * FROM recipeTable r JOIN ingredintsTable i ON r.RecId= i.RecId WHERE i.IngredientId IN (1,2) GROUP BY r.id HAVING ( COUNT(r.id) > 1 ) 

Answer 2

 SELECT * FROM repice r WHERE EXISTS ( SELECT NULL FROM ingredients i WHERE i.recipe_id = r.id AND i.ingredient_id IN (1, 2, 3, 4) LIMIT 1 OFFSET 3 ) 

The OFFSET parameter must be n - 1 , where n is the number of ingredients in the list.

This suggests that the combination (recipe_id, ingredient_id) unique.

Answer 3

You have not declared 'd' in the HAVING clause. I assume you wanted to put "r" there.