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.

Answer 1

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;