
Question:
I have four tables:
cuisines(id, name); recipes(id, cuisine_id, name, picture); ingredients(id, name); ingredients_recipes(ingredient_id, recipe_id);
the last table references the Many-To-Many relationship between recipes and ingredients. How can I select all cuisines which contain tomatoes (i.e. the cuisine has recipes which have ingredients which are called "tomatoes")?
I am using this in SQLite but I guess it's the same in all SQL languages?
Solution:1
Join them all according to the foreign keys, filter on ingredient_name = 'Tomatoe', group by all columns of the cuisines table you're interested in.
SELECT cuisine.id, cuisine.name FROM cuisine INNER JOIN recipe on recipe.cuisine_id = cuisine.id INNER JOIN ingredients_recipes ir ON ir.recipe_id = recipe.id INNER JOIN ingredients on ingredients.id = ir.ingredient_id WHERE ingredients.name = 'Tomatoe' GROUP BY cuisine.id, cuisine.name
Solution:2
You may want to try INNER JOIN
ing all four tables, as in the following example:
SELECT DISTINCT c.name FROM cuisines AS c INNER JOIN recipes AS r ON (r.cuisine_id = c.id) INNER JOIN ingredients_recipes AS ir ON (ir.recipe_id = r.id) INNER JOIN ingredients AS i ON (i.id = ir.ingredient_id) WHERE i.name = 'tomatoes';
Solution:3
Subquery variant:
SELECT name FROM cuisines WHERE id IN ( SELECT cuisine_id FROM recipes r JOIN ingredients_recipes ir ON r.id = ir.recipe_id JOIN ingredients i ON ir.ingredient_id = i.id WHERE i.name = 'Tomatoes' )
Solution:4
I'd suggest using subqueries instead of the joins. Something like...
SELECT * FROM cuisine WHERE cuisine_id IN ( SELECT cuisine_id FROM recipe WHERE recipe_id IN ( SELECT recipe_id FROM recipe_ingredients WHERE ingredient_id IN ( SELECT id FROM ingredients WHERE TOUPPER(name) LIKE '%TOMATO%')));
In any case, I think you have to be careful with the match on the word; more so than other correspondents have been, as you don't want to miss ingredients like 'Four small tomatoes' or 'One large tomato' or 'Tomato puree'.
Solution:5
EXISTS variant:
SELECT name FROM cuisines c WHERE EXISTS ( SELECT NULL FROM recipes r JOIN ingredients_recipes ir ON r.id = ir.recipe_id JOIN ingredients i ON ir.ingredient_id = i.id and i.name = 'tomatoes' WHERE r.cuisine_id = c.id )
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon