My group is building a website in our databases class, and I can't figure out how to write a query I need. There are a few tables in the database. The ones this query will be using are called Recipes, Ingredients, and a junction table called RecipeIngredients. Here is what each table contains:
Recipes:
recipe_id
recipe_name
recipe_creator
recipe_rating
Ingredients:
ingredient_id
ingredient_name
RecipeIngredients:
recipe_id (foreign key)
ingredient_id (foreign key)
PK_recipe_ingredients (primary key)
The website we've created allows the user to search for recipes by ingredient. This query needs to take that searched ingredient (defined as searchTerm in our code) and select all recipes that contain that ingredient. Because we have a junction table, that requires the use of JOIN, which I'm still trying to understand. This is what I have currently, but it's not complete and I don't think it's entirely correct, anyway.
Select * from Recipes
JOIN Ingredients ON Ingredients.ingredient_id = RecipeIngredients.ingredient_id
JOIN RecipeIngredients ON Recipes.recipe_id = RecipeIngredients.recipe_id
Obviously it needs more, like the searchTerm, but I can't figure out where to stick it. Any ideas? I know the question is oddly specific, but we have a lot of good comp sci folks, so I figured I'd ask here, as well.