Filtering SQL results for recipe database

[yamel115]

Hello all, I hope I can get some help with this. I am still pretty new to SQL and now after three days of tinkering with this code I have come to a stand still. I am trying to create a personal database on which I can store my recipes by book and be able to display my recipes based based on the ingredients I have on hand. Now I'm trying to make that page. This is the code

SELECT Recipes.recipe_id, Recipes.name_of_recipe, Category.category_name, Ingredients.availabilityFROM Recipes, Category, Ingredients, Recipe_IngredientsWHERE Recipes.category_id = Category.category_id AND Recipe_Ingredients.ingredient_id = Ingredients.ingredient_id AND Recipe_Ingredients.recipe_id = Recipes.recipe_idGROUP BY Ingredients.availability, Recipes.name_of_recipeORDER BY Recipes.name_of_recipe

and these are the results that I get so farCaribbean Chicken with Creamy Avocado Main Dishes yesCaribbean Chicken with Creamy Avocado Main Dishes noChicken and Mushroom Marsala Main Dishes yesChicken with Chipotle Sauce Main Dishes yesAs you can see, the caribbean chicken shows up twice because I'm missing ingredients and therefore there is a "no" on part of the ingredients side. If anyone can help me with the SQL code, it would be greatly appreciated. I can wait to have this recipe database up a running

[justsomeguy]

You'll need to use a subquery to filter out records where any ingredient is not available. Something like this:recipe_id NOT IN (SELECT recipe_id FROM recipe_ingredients WHERE ingredient_id IN (SELECT ingredient_id FROM ingredients WHERE availablity='no'))

[yamel115]

You'll need to use a subquery to filter out records where any ingredient is not available. Something like this:recipe_id NOT IN (SELECT recipe_id FROM recipe_ingredients WHERE ingredient_id IN (SELECT ingredient_id FROM ingredients WHERE availablity='no'))

Thank you SO, SO, SO, SO MUCH, that is exactly what I needed!!