Difficulty Returning Accurate Value for Data in Multiple Tables

I have been having trouble designing a query for a few days and it has me dumbfounded. I’m hoping someone can help. For the sake of simplicity, imagine I have these three tables:

recipe table
recipe_id, recipe name
25, Cake
36, Omelet

ingredients table
ingredient_id, recipe_id, ingredient
1, 25, flour
2, 25, eggs
3, 25, sugar
4, 36, eggs
5, 36, oil
6, 36, butter

pantry table
pantry_id, user_id, ingredient
1, 1000, eggs
2, 1000, oil
3, 1000, butter
4, 1000, sugar

I’m attempting to design a query that will return the recipe_id 36 since this is the recipe in which all ingredients are in the user’s pantry. The fact that this data is stored in 3 tables is what has me confused. I believe I really only need the last two tables but I’ve included the first to complete the example.

This is the query that I designed…

SELECT recipe.recipe_id FROM recipe, ingredients, pantry WHERE recipe.recipe_id = ingredients.recipe_id AND ingredients.ingredient = pantry.ingredient AND pantry.user_id = ‘1000’

I know that this isn’t right because it’s returning all of the rows from the “ingredients” table that match one of the ingredients from the “pantry” table. What I want is for it to only return the recipe_id if ALL of the ingredients for that recipe are available in the pantry.

If one of you could help you would save my day!

SELECT recipe.recipe_id FROM pantry INNER JOIN ingredients ON ingredients.ingredient = pantry.ingredient INNER JOIN recipe ON recipe.recipe_id = ingredients.recipe_id WHERE pantry.user_id = ‘1000’ GROUP BY recipe.recipe_id HAVING COUNT(*) = ( SELECT COUNT(*) FROM ingredients WHERE recipe_id = recipe.recipe_id )

1 Like

Thanks so very much! I’ve learned lot just from this answer. I appreciate you taking the time to think through this and provide a response.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.