CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jill Clover
Jill Clover

Reputation: 2328

How to find rows missing by every group

I have two tables:

Input:

A:

ID col
1  a
1  b
1  c
2  a
2  b
3  x
4  y

B 
ID col
1  a
1  b
2  a

I want to for every ID in B, find rows in A but not in B by every ID.

Output:

ID Col
1  c
2  b

What I tried:

  1. left/right join. I am trying something like select * from a left join b on a.id = b.id where b.id is null
  2. except. select * from a except select * from b

but not sure how to modify it.

Upvotes: 1

Views: 50

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Using a combination of exists and not exists.

select * 
from a
where exists (select 1 from b where a.id=b.id) --id check
and not exists (select 1 from b where a.id=b.id and a.col=b.col) -- col check

Upvotes: 0

Cristian Lupascu
Cristian Lupascu

Reputation: 40516

Assuming you want the values in A for which there are records in B with the same ID, but not the same col, you could do:

select
  a.ID,
  a.col
from A
left join B
  on b.ID = a.ID and b.col = a.col
where A.ID in (select distinct ID from B)  -- B contains this `ID` somewhere...
  and B.ID is null                         -- ...but not with the same `col`

Test it here.

Upvotes: 2

Related Questions