SQL "ContainsAll" query

Question

I have recipes table and ingredients table and table that connect ingredients to the recipes.

I have a list of ingredients, how to write a SELECT statement (or store procedure) that will return a recipes that have ALL given ingredients?

How to write this query for MySQL?

Answer 1

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 should equal to n - 1, where n is the number of ingredients in the list.

This assumes that (recipe_id, ingredient_id) combination is unique.

Answer 2

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(d.id) > 1 )