CODEWITHSUNDEEP
sql
Tim
Tim

Reputation: 35

SQL server query - return invoice that only has certain records

Say I have the following record table (table name: SALESINVOICE):

invoiceid     item           itemgroup
---------     ----           ---------
12345         apple          fruit
12345         banana         fruit
12345         banana         fruit
12345         chocolate bar  candy
24680         apple          fruit
24680         banana         fruit
24680         orange         fruit
36912         pear           fruit
36912         cherry         fruit
98765         strawberry     fruit
98765         bread          grain

I would like to query the table so it will return the "invoiceid" that only has "fruit" itemgroup and no other itemgroup. So the output would look like:

invoiceid     item           itemgroup
---------     ----           ---------
24680         apple          fruit
24680         banana         fruit
24680         orange         fruit
36912         pear           fruit
36912         cherry         fruit

How would I go about querying this in SQL Server 2016? My apologies for not providing a work in progress code. I hope the community can help me out.

Thank.

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.invoiceid = t.invoiceid and 
                        t2.itemgroup <> 'fruit'
                 );

Upvotes: 1

Related Questions