Member Avatar for yamel115

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.

  • 2 Contributors
  • 1 Reply
  • 111 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for smantscheff

Show your table structure, show some test data, show what should work and what doesn't.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.