CODEWITHSUNDEEP
sql
cm48
cm48

Reputation: 35

How would I find the ingredients that belong to only one recipe?

INGREDIENT(ingredient-id,name,price-ounce)
RECIPE(recipe-id,name,country,time)
USES(rid,iid,quantity)

rid is a foreign key to recipe-id and iid is a foreign key ingredient_id

How would I find the ingredients that belong to only one recipe?

I've tried a few ways but am unsure how to approach this.

Upvotes: 1

Views: 113

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I would just use aggregation:

select u.iid
from uses u
group by u.iid
having count(distinct u.rid) = 1;

If you want the name (instead of the id), then you can join to the ingredients table.

Note: the count(distinct) allows an ingredient to be used multiple times for the same recipe. If that is not allowed, then use count(*) instead.

Upvotes: 1

Robert Kock
Robert Kock

Reputation: 6018

Extract the ingedients that appear in exactly one recipe (HAVING COUNT(*) = 1):

SELECT ingredient_id,
       name
FROM   (SELECT   ingredient.ingredient_id,
                 ingredient.name,
                 COUNT(*)
        FROM     ingredient
        JOIN     uses
          ON     ingredient.ingredient_id = uses.iid
        GROUP BY ingredient.ingredient_id,
                 ingredient.name
        HAVING   COUNT(*) = 1
       )

Upvotes: 2

Related Questions