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!