Find Unmatched Records in Same Table

[dfrazell]

How can I find all the unmatched records in the same table? I need to find all the parts that aren’t setup in a second warehouse. The table contains a record for each warehouse and part combination. The unique key to the table is WH_ID and PART_ID.

Example:

Table Name: WH_PART

WH_ID & PART_ID
---------------
A, 1
B, 1
A, 2
B, 3

So the query should return:
B, 2 and A, 3

 

[PWise]

Try

Select *
from (
Select Wh.WH_ID,Part.PART_ID
From WH_PART Wh,WH_PART Part
)Whpart
left join WH_PART whpart1
on Whpart.WH_ID=whpart1.WH_ID
And Whpart.PART_ID=whpart1.PART_ID
Where whpart1.WH_ID is null

 

[markros]

If you only have 2 warehouses:

select case when ISNULL(a.wh_ID,b.wh_ID) = 'A' then 'B' else 'A' end as WH_ID, coalesce(a.Part_ID, b.Part_ID) as Part_ID
from wh_Part a full join Wh_Part B
on a.wh_ID = 'A' and a.Part_ID = b.Part_ID and b.Wh_ID = 'B'
where a.Wh_ID IS NULL or b.Wh_ID IS NULL

Alternatively, you can do

;with BMissed as (select 'B' as Wh_ID, a.Part_ID from
wh_part A where A.Wh_ID = 'A' and not exists (select 1 from Wh_Part B
where B.Wh_ID = 'B' and b.part_ID = a.Part_ID),
AMissed as (select 'A' as Wh_ID, a.Part_ID from
Wh_Part a where a.Wh_ID = 'B'
and not exists (select 1 from Wh_Part b
where b.WH_ID = 'A' and b.Part_ID = a.Part_ID)

select * from AMissed
UNION ALL
select * from BMissed

PluralSight Learning Library