CODEWITHSUNDEEP
sqlsql-server
user3818587
user3818587

Reputation: 303

SQL - Getting distinct ID that the data is not in the record

I have a table named Supplier. 

ID  SupplierGroup
101 FAC_A
101 FAC_B
101 FAC_C
101 FAC_D
202 FAC_A
202 FAC_B
202 FAC_D
303 FAC_B
303 FAC_C
303 FAC_D
404 FAC_A
404 FAC_B
404 FAC_C
404 FAC_D
505 FAC_C
505 FAC_D

I have to get the distinct ID of the Suppliers not having FAC_A as the SupplierGroup. I used this query..

select distinct ID
  from Supplier
  where SupplierGroup not in ('FAC_A')

but there's something wrong with my query. it resulted to 

ID
101
202
303
404
505

It should get the result of 

ID 
303
505

Upvotes: 1

Views: 50

Answers (2)

gofr1
gofr1

Reputation: 15977

Another way with OUTER APPLY:

SELECT DISTINCT s.ID
FROM Supplier s
OUTER APPLY (SELECT TOP 1 * FROM Supplier WHERE SupplierGroup IN ('FAC_A') AND ID = s.ID) p
WHERE p.ID IS NULL

Output:

ID
303
505

Upvotes: 1

fthiella
fthiella

Reputation: 49049

One solution is to use NOT EXISTS:

select distinct s.ID
from Supplier s
where
  not exists (select * from Supplier
              where supplier.ID=s.ID and
              supplier.SupplierGroup ='FAC_A')

another solution is to use GROUP BY and count the FAC_A values:

select ID
from Supplier
group by ID
having
  sum(case when SupplierGroup ='FAC_A' then 1 else 0 end)=0

Upvotes: 2

Related Questions