MySQL: Query to obtain recipes using all given ingredients

Question

I have the following simplified tables:

CREATE TABLE recipe(id int, name varchar(25));  
CREATE TABLE ingredient(name varchar(25));  
CREATE TABLE uses_ingredient(recipe_id int, name varchar(25));

I want to make a query that returns all id's of recipes that contain both Chicken and Cream.

I have tried

SELECT recipe_id FROM uses_ingredient INNER JOIN  
(SELECT * FROM ingredient WHERE name="Chicken" OR name="Cream")  
USING (name) GROUP BY recipe_id  
HAVING COUNT(recipe_id) >= (SELECT COUNT(*) FROM theme);  

which gives me :"ERROR 1248 (42000): Every derived table must have its own alias" and is probably wrong too.

Next I tried

SELECT recipe_id FROM 
(SELECT * FROM ingredient WHERE name="Chicken" OR name="Cream") AS t 
INNER JOIN uses_ingredient USING (name) 
GROUP BY recipe_id HAVING 
COUNT(recipe_id)>= (SELECT COUNT(*) FROM t);

which gives "ERROR 1146 (42S02): Table 'recipedb.t' doesn't exist"

I want to avoid creating temporary tables including using ENGINE=MEMORY.

Answer 1

There is likely a better way to think of it, but I see the answer as the intersection of two sets: those recipes that have chicken in them and those recipes that have cream in them. This might not work for you if you simplified more than just the number of columns. But I tried it with a handful of records and it seems fine.

SELECT CH.Recipe_Id
FROM
  (SELECT Recipe_Id
  FROM Uses_Ingredient
  WHERE Name = 'Chicken') CH
INNER JOIN 
  (SELECT Recipe_Id
  FROM Uses_Ingredient
  WHERE Name = 'Cream') CR
  ON CR.Recipe_Id = CH.Recipe_Id

Answer 2

I agree with MJB; it is the intersection of two sets. So I'd just get the two sets and see what's in both of them...

SELECT *
    FROM recipe
    WHERE EXISTS (SELECT *
                      FROM uses_ingredient
                      WHERE uses_ingredient.recipe_id = recipe.recipe_id AND
                            uses_ingredient.name      = "Chicken") AND
          EXISTS (SELECT *
                      FROM uses_ingredient
                      WHERE uses_ingredient.recipe_id = recipe.recipe_id AND
                            uses_ingredient.name      = "Cream");

Answer 3

The other suggestions are good, but here is another way. This actually seems like what you were trying to do in your query. Although I don't know why you have and "ingredient" table when the same information (name) seems to be contained in "uses_ingredient".

SELECT recipe_id, count(*) c FROM uses_ingredient
WHERE name="Chicken" OR name="Cream"
GROUP BY recipe_id
HAVING c=2