Reputation: 1835
I'm using SQL and need to select the idR and a count of how many ingredients are in that idR.
How would I go about doing this?
idR, recipeTitle, prepText, cuisineType, mealType
)idI, ingrDesc
)idR*, idI*
)This is what I've tried
SELECT
RECIPE.idR
FROM
(SELECT COUNT(*)
FROM INGREDIENT
GROUP BY INGREDIENT.idI
ORDER BY idI.id DESC)
but I'm just wanting a count of how many ingredients are in each idR.
Any help?
Upvotes: 1
Views: 116
Reputation: 835
SELECT *,INGREDIENT.INGREDIENT_Count
FROM RECIPE INNER JOIN (SELECT idR,COUNT(*) INGREDIENT_Count
FROM RecipIngr
GROUP BY idR) INGREDIENT ON INGREDIENT.idR RECIPE.idR
Using this Query you can get Ingredient Count for each Recipe.
Upvotes: 0
Reputation: 4208
RecipIngr is the one that shows you the list of ingredients you need for each idR? the code would be
SELECT idR, count(*)
FROM RecipIngr
GROUP BY idR
EDIT: I misunderstood earlier but this should work now
Upvotes: 0
Reputation: 7197
If I understand your query, you could try this:
select recipe.idr, count(ingredient.idi)
from ingredient, recipe, recipingr
where ingredient.idr=recipingr.idr
and recipingr.idi=ingredient.idi
group by recipe.idr
Upvotes: 0
Reputation: 1208
If I understand your schema,
SELECT idR, count(*)
FROM RecipIngr
GROUP BY idR
Upvotes: 2
Reputation: 263943
use only the association table if you are only interested on the IDR
SELECT iDR, COUNT(*) totalIngredients
FROM RecipIngr
GROUP BY iDR
but if you want to get the TITLE
SELECT a.iDR, a.Title,
COUNT(*) totalIngredients
FROM Recipe a
INNER JOIN RecipIngr b
ON a.idr = b.idr
GROUP BY a.iDR, a.Title
To further gain more knowledge about joins, kindly visit the link below:
Upvotes: 0