Tutorial :search in joint tables

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 JOINing 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