How can I find all the rows in a table that exclusively have related rows in a given list?

Question

I have three tables. Think of them as the following:

  Recipes
  id | name
  1  | Cookies
  2  | Soup
  ...


  Ingredients
  id | name
  1  | flour
  2  | butter
  3  | chicken
  ...

  Recipe_Ingredient
  recipe_id | ingredient_id
  1         | 1
  1         | 2
  2         | 3

Hopefully you get the idea. What I'd like is a query where I can find all recipes that have ingredients that are a subset of a given set of ingredients.

The idea being that I'd like to have a list of all the things I can make with what I have on hand (but of course not EVERYTHING I have on hand.)

I tried implementing this with various levels of subqueries and correlated subqueries with EXISTS but had no luck. I also tried to use HAVING and COUNT, but that only seems to work for me if I want something that uses ALL the ingredients I have on hand.

So you want recipes that ALL their ingredients are in the "available" ingredients list. Try the [sql-match-all] tag or the links on the right, under **Related** header. — ypercubeᵀᴹ, Mar 31 '12 at 07:14
This is called [The SQL of Relational Division](http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/) — ypercubeᵀᴹ, Mar 31 '12 at 07:15
@ypercube wow, awesome. Yeah, that's actually what I tried but I must have gotten my logic messed up somehow. Thanks for the awesome link — Frew Schmidt, Mar 31 '12 at 07:16
This question has more than 10 ways to achieve this [How to filter SQL results in a has-many-through relation](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation). Benchmarked, too. The more general (if your available ingredients are in a table or a list and you don't want to create SQL queries dynamically), are answers 1,2,7,8. — ypercubeᵀᴹ, Mar 31 '12 at 07:17

oriolussteerii · Answer 1 · 2012-03-31T17:55:49.107

    mysql>
    mysql> select * from ingredients;
    +------+---------------+-----------+
    | id   | name          | available |
    +------+---------------+-----------+
    |    1 | salt          | n         |
    |    2 | sugar         | n         |
    |    3 | flour         | n         |
    |    4 | butter        | n         |
    |    5 | vanilla       | n         |
    |    6 | baking powder | n         |
    |    7 | egg           | n         |
    +------+---------------+-----------+
    7 rows in set (0.00 sec)

    mysql> select * from recipes;
    +------+---------------+
    | id   | name          |
    +------+---------------+
    |    1 | cookie        |
    |    2 | soup          |
    |    3 | xtreme flavor |
    +------+---------------+
    3 rows in set (0.00 sec)

    mysql> select * from recipe_ingredient;
    +-----------+---------------+
    | recipe_id | ingredient_id |
    +-----------+---------------+
    |         1 |             1 |
    |         1 |             2 |
    |         1 |             3 |
    |         1 |             4 |
    |         1 |             5 |
    |         1 |             6 |
    |         1 |             7 |
    |         2 |             1 |
    |         2 |             7 |
    |         3 |             4 |
    |         3 |             3 |
    +-----------+---------------+
    11 rows in set (0.00 sec)

    mysql>
    mysql> update ingredients set available = 'n';
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 7  Changed: 0  Warnings: 0

    mysql>
    mysql> update ingredients set available = 'y'
        -> where id in  (1,2,3,4,5,6,7);
    Query OK, 7 rows affected (0.00 sec)
    Rows matched: 7  Changed: 7  Warnings: 0

    mysql>
    mysql> select recipes.name from
        -> (select recipe_id, available from
        -> recipe_ingredient,
        -> ingredients
        -> where ingredient_id = ingredients.id
        -> group by  recipe_id, available) x, recipes
        -> where recipes.id = x.recipe_id
        -> group by x.recipe_id
        -> having count(*) = 1
        -> and max(x.available) = 'y';
    +---------------+
    | name          |
    +---------------+
    | cookie        |
    | soup          |
    | xtreme flavor |
    +---------------+
    3 rows in set (0.06 sec)

    mysql>
    mysql> update ingredients set available = 'n';
    Query OK, 7 rows affected (0.00 sec)
    Rows matched: 7  Changed: 7  Warnings: 0

    mysql>
    mysql> update ingredients set available = 'y'
        -> where id in  (1,7);
    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2  Changed: 2  Warnings: 0

    mysql>
    mysql> select recipes.name from
        -> (select recipe_id, available from
        -> recipe_ingredient,
        -> ingredients
        -> where ingredient_id = ingredients.id
        -> group by  recipe_id, available) x, recipes
        -> where recipes.id = x.recipe_id
        -> group by x.recipe_id
        -> having count(*) = 1
        -> and max(x.available) = 'y';
    +------+
    | name |
    +------+
    | soup |
    +------+
    1 row in set (0.06 sec)

    mysql>
    mysql>
    mysql> update ingredients set available = 'n';
    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 7  Changed: 2  Warnings: 0

    mysql>
    mysql> update ingredients set available = 'y'
        -> where id in  (4,3);
    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2  Changed: 2  Warnings: 0

    mysql>
    mysql> select recipes.name from
        -> (select recipe_id, available from
        -> recipe_ingredient,
        -> ingredients
        -> where ingredient_id = ingredients.id
        -> group by  recipe_id, available) x, recipes
        -> where recipes.id = x.recipe_id
        -> group by x.recipe_id
        -> having count(*) = 1
        -> and max(x.available) = 'y';
    +---------------+
    | name          |
    +---------------+
    | xtreme flavor |
    +---------------+
    1 row in set (0.05 sec)

    mysql>
    mysql>
    mysql> update ingredients set available = 'n';
    Query OK, 3 rows affected (0.00 sec)
    Rows matched: 7  Changed: 3  Warnings: 0

    mysql>
    mysql> update ingredients set available = 'y'
        -> where id in  (1,3,7);
    Query OK, 3 rows affected (0.00 sec)
    Rows matched: 3  Changed: 3  Warnings: 0

    mysql>
    mysql> select recipes.name from
        -> (select recipe_id, available from
        -> recipe_ingredient,
        -> ingredients
        -> where ingredient_id = ingredients.id
        -> group by  recipe_id, available) x, recipes
        -> where recipes.id = x.recipe_id
        -> group by x.recipe_id
        -> having count(*) = 1
        -> and max(x.available) = 'y';
    +------+
    | name |
    +------+
    | soup |
    +------+
    1 row in set (0.06 sec)

Does this correctly work if I have ingredients 1, 3, and 7 available? That is, it should return soup, since I can make soup with 1 and 7. — Frew Schmidt, Mar 31 '12 at 14:18
@Frew, yes it does return 'soup' when 1, 3, and 7 are available. I added the results above. — oriolussteerii, Mar 31 '12 at 17:59
Awesome, thanks; I'll see if this works for me and if so, YOU GET THE CREDIT :-) — Frew Schmidt, Mar 31 '12 at 19:52
Actually, I'm having a lot of trouble figuring out how to convert this to my schema; that is, a list of ids or a subquery instead of a flag column of what's available. Can I get some help? — Frew Schmidt, Mar 31 '12 at 20:06

score 1 · Accepted Answer · edited Apr 06 '12 at 03:34

Test this if it works. Assuming that you have your (unique) ingredient ids available in a table called ingredients_avail, this query should show the recipe id that has complete ingredients:

    SELECT recipe_id
    FROM [select recipe_id, count(*) as num_of_ingredients from recipe_ingredient group by recipe_id]. AS x, [select recipe_ingredient.recipe_id as recipe_id, count(*) as num_of_ingredients 
    from recipe_ingredient, ingredients_avail               
    where               
     recipe_ingredient.ingredient_id = ingredients_avail.ingredient_id
    group by recipe_ingredient.recipe_id]. AS y               
    WHERE x.recipe_id = y.recipe_id and               
    x.num_of_ingredients = y.num_of_ingredients;

Additionally, here it is in a more typical syntax:

  SELECT x.recipe_id 
    FROM ( 
      SELECT recipe_id, count(*) as num_of_ingredients 
        FROM recipe_ingredients
      GROUP BY recipe_id
     ) x, ( 
      SELECT recipe_id, count(*) as num_of_ingredients 
        FROM recipe_ingredients
        JOIN ingredients_avail 
          ON recipe_ingredients.ingredient_id = ingredients_avail.ingredient_id 
      GROUP BY recipe_id
     ) y 
  WHERE x.recipe_id = y.recipe_id AND x.num_of_ingredients = y.num_of_ingredients;

How can I find all the rows in a table that exclusively have related rows in a given list?

2 Answers2