Missing Records - Not In Table 2

[guyandshawn]

Guy writes "I have a Table with two fields.

Parent_ID
Equipment_ID

Every Unique Parent_ID needs to have one record where the Parent_ID and the Equipment_ID matches.
I want to find the Unique Parent_ID's where there is no record where the Parent_ID and the Equipment_ID matches.

Example:
There are two children records and one Parent record. The Parent Record is where the Parent_ID and the Equipment_ID are the same. The Children are where the Equipment_ID does not match the Parent_ID.
Equipment_ID------------Parent_ID
A001--------------------Cleveland 1
A002--------------------Cleveland 1
Cleveland 1-------------Cleveland 1


Example 2:
In this example the Parent Record is missing. There is no record where the Equipment_ID is the same as the Parent_ID
Equipment_ID---------Parent_ID
B001-----------------Cleveland 2
B002-----------------Cleveland 2

The result would show "Cleveland 2"

Thank you so much for your time on this.
Guy

 

[PHV]

SELECT A.Parent_ID
FROM (SELECT Parent_ID FROM yourTable WHERE Parent_ID<>Equipment_ID GROUP BY Parent_ID) A
LEFT JOIN (SELECT Parent_ID FROM yourTable WHERE Parent_ID=Equipment_ID) B
ON A.Parent_ID=B.Parent_ID
WHERE B.Parent_ID IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[guyandshawn]

Wow, A reply in 11 minutes which works !!!
I can not thank you enough PHV.
You made my week.....
Thanks !!! Guy