hi

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.