CODEWITHSUNDEEP
sqlsortingjoininner-join
user182
user182

Reputation: 1835

SQL Query count

HI there I have this table,

    Recipe = (idR, recipeTitle, prepText, cuisineType, mealType)
    Ingredient = (idI, ingrDesc)
    RecipIngr = (idR*, idI*)

and I'm trying to query a list for ingrDesc with a count of how many recipies that ingrDesc is in. I want to list only those ingrDesc that occur more than 10 times.

Here's what I have:

   SELECT a.idI, a.recipeTitle 
   FROM Recipe a
   INNER JOIN recpingr b
   ON a.idr = b.idr
   WHERE a.preptext = '>10'

Any help as I don't know how to carry on with this query

Upvotes: 3

Views: 64

Answers (2)

Matthew
Matthew

Reputation: 198

You need to use a group by clause and having. I have created a quick sample here but my sample data does not go up to 10 so I used any ingredient that was used more than once (> 1).

Here is the sample data:

    create table dbo.recipe (
        idR             int             not null,
        recipeTitle     varchar(100)    not null,
        prepText        varchar(4000)   null,
        cuisineType     varchar(100)    null,
        mealType        varchar(100)    null
    ) 
    go

    insert into dbo.recipe values (1, 'Eggs and Bacon', 'Prep Text 1', 'American', 'Breakfast')
    insert into dbo.recipe values (2, 'Turkey Sandwich', 'Prep Text 2', 'American', 'Lunch')
    insert into dbo.recipe values (3, 'Roast Beef Sandwich', 'Prep Text 3', 'American', 'Lunch')
    go

    create table dbo.ingredient (
        idI             int             not null,
        ingrDesc        varchar(200)    not null
    )
    go

    insert into dbo.ingredient values (1, 'Large Egg')
    insert into dbo.ingredient values (2, 'Bacon');
    insert into dbo.ingredient values (3, 'Butter');
    insert into dbo.ingredient values (4, 'Sliced Turkey');
    insert into dbo.ingredient values (5, 'Lettuce');
    insert into dbo.ingredient values (6, 'Tomato');
    insert into dbo.ingredient values (7, 'Onion');
    insert into dbo.ingredient values (8, 'Bread');
    insert into dbo.ingredient values (9, 'Mustard');
    insert into dbo.ingredient values (10, 'Horseradish');
    insert into dbo.ingredient values (11, 'Sliced Roast Beef');
    go

    create table dbo.recipingr(
        idR             int             not null,
        idI             int             not null
    )
    go

    insert into dbo.recipingr values (1, 1);
    insert into dbo.recipingr values (1, 2);
    insert into dbo.recipingr values (2, 4);
    insert into dbo.recipingr values (2, 5);
    insert into dbo.recipingr values (2, 6);
    insert into dbo.recipingr values (2, 7);
    insert into dbo.recipingr values (2, 8);
    insert into dbo.recipingr values (2, 9);
    insert into dbo.recipingr values (3, 11);
    insert into dbo.recipingr values (3, 10);
    insert into dbo.recipingr values (3, 8);
    insert into dbo.recipingr values (3, 6);
    insert into dbo.recipingr values (3, 5);
    go

Here is the query:

    select 
        i.ingrDesc, 
        count(*) ingrCount
    from
        dbo.recipe r
        inner join dbo.recipingr ri on ri.idR = r.idR
        inner join dbo.ingredient i on i.idI = ri.idI
    group by 
        i.ingrDesc
    having
        count(*) > 1

Upvotes: 1

PinnyM
PinnyM

Reputation: 35531

Use GROUP BY with HAVING:

SELECT i.idI, i.ingrDesc, COUNT(*)
FROM Ingredient i
INNER JOIN RecipIngr ri ON i.idI = ri.idI
GROUP BY i.idI, i.ingrDesc
HAVING COUNT(*) > 10

Upvotes: 3

Related Questions