SQL query through an intermediate table

Question

Given the following tables:

Recipes | id | name | 1  | 'chocolate cream pie' | 2  | 'banana cream pie' | 3  | 'chocolate banana surprise'  Ingredients | id | name | 1  | 'banana' | 2  | 'cream' | 3  | 'chocolate'  RecipeIngredients | recipe_id | ingredient_id |     1     |      2 |     1     |      3 |     2     |      1 |     2     |      2 |     3     |      1 |     3     |      3 

How do I construct a SQL query to find recipes where ingredients.name = 'chocolate' and ingredients.name = 'cream'?

Answer 1

Use:

  SELECT r.name     FROM RECIPES r     JOIN RECIPEINGREDIENTS ri ON ri.recipe_id = r.id     JOIN INGREDIENTS i ON i.id = ri.ingredient_id                       AND i.name IN ('chocolate', 'cream') GROUP BY r.name   HAVING COUNT(DISTINCT i.name) = 2 

The key point here is that the count must equal the number of ingredient names. If it's not a distinct count, there's a risk of false positives due to duplicates.

Answer 2

This is called relational division. A variety of techniques are discussed here.

One alternative not yet given is the double NOT EXISTS

SELECT r.id, r.name FROM Recipes r WHERE NOT EXISTS (SELECT * FROM Ingredients i                   WHERE name IN ('chocolate', 'cream')                   AND NOT EXISTS                       (SELECT * FROM RecipeIngredients ri                        WHERE ri.recipe_id = r.id                        AND ri.ingredient_id = i.id))