CODEWITHSUNDEEP
sqloracle-database
Tommy Yap
Tommy Yap

Reputation: 101

sql select rows with duplicate data

I need help with this select statement in my relational table (both supplier and products are composite primary key which uniquely identified each rows

Supplier    Products
ABC         Toys
ABC         Snacks
ZXC         Snacks
ZXC         Food
QWE         Toys
ABC         Food

I need to find the supplier that does not supply toys so i shoud only get ZXC

I try the following but it give me ABC, ZXC

select distinct Supplier
from table 
where NOT (Products ='Toys');

(I am using oracle) how should my query be? Thanks

Upvotes: 0

Views: 66

Answers (4)

davegreen100
davegreen100

Reputation: 2115

select distinct supplier 
from table
where supplier not in (select supplier from table where products = 'Toys')

Upvotes: 5

xQbert
xQbert

Reputation: 35323

Lots of ways to do this here's a couple. I've found exists and not exists to generally be the fastest but it always depends on indexes and your system. So look at execution plan for best use in your environment.

using a left join 

SELECT T.SUPPLIER 
FROM TABLE T
LEFT JOIN TABLE T2 
 on T.Supplier = T2.Supplier
and T2.Products = 'Toys'
where T2.Products is null

Using not exists.

SELECT supplier
FROM table T
WHERE NOT EXISTS (SELECT 1 
                  FROM Table A 
                  WHERE T.Supplier = A.Supplier and Products = 'Toys')

Upvotes: 0

SQLChao
SQLChao

Reputation: 7847

Another way to do it with NOT EXISTS

select distinct
    supplier 
from 
    table t1
where 
    not exists (select * from table t2 where t1.supplier = t2.supplier and t2.products = 'Toys')

Upvotes: 1

potashin
potashin

Reputation: 44581

You can group by Supplier and use having clause evaluated to true if conditional count is equal to 0 (no matches within group):

select Supplier
from table 
group by Supplier
having count(case when Products = 'Toys' then Products end) = 0

Upvotes: 2

Related Questions