I am having trouble merging two queries. I want to be able to search my recipe inventory by ingredient, but I also want it to tell me how many ingredients I am missing for that certain recipe. I am able to do a search by just ingredient and I am also able to do a search by just showing me how many ingredients I'm missing, but I have not been successful to combine the two. This is my code
SELECT Recipes.name_of_recipe,Recipe_Ingredients.quantity, Recipe_Ingredients.measurement, Ingredients.ingredient_name, Category.category_name, Recipes.recipe_id, COUNT(Ingredients.availability)
FROM Recipes, Category, Ingredients, Recipe_Ingredients
WHERE Recipes.category_id = Category.category_id AND Ingredients.ingredient_id = Recipe_Ingredients.ingredient_id AND Recipe_Ingredients.recipe_id = Recipes.recipe_id AND Ingredients.ingredient_name LIKE varIngredient AND Recipe_Ingredients.recipe_id = Recipes.recipe_id AND Ingredients.availability IN (SELECT Ingredients.availability FROM Ingredients WHERE Ingredients.availability = 'no')
If anyone can help, that'd be great, this is the last piece of the SQL puzzle in order to make my site work the way I want it to work.