MySQL: Query to obtain recipes using all given ingredients.
- by John_A
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.
