SQL Query Counting Too Many 1

[Signit]

I am trying to write a SQL Query which will count the number of ingredients associated with a recipe in addition to finding out the average rating for that recipe. I have the following tables involved in this query: recipes, recipeIngredients, and ratingList.

recipes holds recipeName

recipeIngredients is a transition table between an ingredients table and recipes recipeID identifies recipe and ingredientID identifies specified ingredient

ratingList is a list table w/ recipeID as a foreign key

The problem that I am having is that the below query counts the number of instances in the rating table. For instance if a recipe had 9 ratings and 2 ingredients my total for ingredientCount would be 18 (9*2). I am sure there is a way to do this (pretty sure it has to do w/ my INNER JOIN but I can't figure out a different way of getting there).

My goal is to select the recipeName, number of ingredients associated w/ that recipe and the avgRating for that recipe. Any help would be much apprecited!

SQL QUERY:

SELECT recipes.recipeName, Avg(ratingList.ratingValue) AS avgRatingValue, 
recipeIngredients.recipeID, count(ingredientID) AS ingredientCount 
FROM (recipes INNER JOIN ratingList ON recipes.recipeID=ratingList.recipeID) INNER JOIN recipeIngredients ON recipes.recipeID = recipeIngredients.recipeID
GROUP BY recipeIngredients.recipeID, recipes.recipeName

 

[Golom]

You need to do this with a subquery. As long as you are joining to the "ratinglist" table you will get "ratinglist * recipeIngredients" records. Try this
[blue][tt]
SELECT R.recipeName,

(Select Avg(ratingValue)
From ratingList L
Where L.recipeID = R.RecipeID ) AS AvgRatingValue,

R.recipeID,
Count(I.IngredientID) AS IngredientCount

FROM (Recipes R INNER JOIN RecipeIngredients I
ON R.RecipeID = I.RecipeID

GROUP BY R.recipeID, R.RecipeName
[/tt][/blue]

 

[Signit]

Golom,

I apprecaite your help. I am trying to make one modification and am having problems. After some second thought I need to also count the total number of ingredients associated w/ each recipe in addition to counting the total number of ingredients associated w/ each recipe.

SELECT R.recipeName, 
(SELECT Avg(ratingValue) FROM ratingList L WHERE L.recipeID = R.recipeID) AS avgRatingValue,
(SELECT COUNT(recipeIngredients.ingredientID) FROM recipeIngredients WHERE recipes.recipeID = recipeIngredients.RecipeID ) AS totIngredientCount,

R.recipeID, COUNT(I.ingredientID) AS myIngredientCount

FROM recipes AS R INNER JOIN recipeIngredients AS I ON R.recipeID = I.RecipeID

WHERE (I.ingredientID='ing011' OR I.ingredientID='ing015' OR I.ingredientID='ing032')

GROUP BY R.recipeID, R.recipeName;

I apologize for what is probably a buchering of your code. What I am looking to do is have a total for the number of ingredients specified as well as total number of ingredients for a particular recipe. I appreciate your continued support!

 

[Signit]

I got it. Ended up w/ this:

SELECT *
FROM (SELECT TOP 4 * FROM (SELECT TOP 5  R.recipeName, (SELECT Avg(ratingValue) FROM ratingList L WHERE L.recipeID = R.recipeID) AS avgRatingValue, (SELECT COUNT(recipeIngredients.ingredientID) FROM recipeIngredients WHERE R.recipeID = recipeIngredients.RecipeID ) AS totIngredientCount, R.recipeID, COUNT(I.ingredientID) AS myIngredientCount
FROM recipes AS R INNER JOIN recipeIngredients AS I ON R.recipeID=I.RecipeID
WHERE (I.ingredientID='ing011' OR I.ingredientID='ing015' OR I.ingredientID='ing032')
GROUP BY R.recipeID, R.recipeName))