CODEWITHSUNDEEP
sqlselectcount
user182
user182

Reputation: 1835

Selecting data from two different tables

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?

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

Answers (6)

SAM
SAM

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

Green Demon
Green Demon

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

Sean Landsman
Sean Landsman

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

Darren
Darren

Reputation: 70814

SELECT RG.IDR, COUNT(RG.IDR)
FROM Recipingr RG
GROUP BY RG.IDR

Upvotes: 0

Aushin
Aushin

Reputation: 1208

If I understand your schema,

SELECT idR, count(*)
FROM RecipIngr
GROUP BY idR

Upvotes: 2

John Woo
John Woo

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

Related Questions