Link to home
Start Free TrialLog in
Avatar of Member_2_99151
Member_2_99151

asked on

SQL Query to get orphaned nodes

Hi all,
I have an interesting query that I am trying to create...

I have a table that contains entities, each of these *may* have a parent (only single level)

What I would like to do is query the table to find entries that say they have a parent, but the parent entry does not exist.

tblData
ID, DESCRIPTION, PARENT
'A','Main Parent','-'
'B','First Child','A'
'C','Second Child','A'
'D','Another Parent','-'
'E','An Orphan','Z'

In this example, I would like to execute a query to get the record relating to 'E'

I've tried a few ways, but can't get it to work!!!

Any ideas would be very much appreciated..

Thanks a lot,

James
ASKER CERTIFIED SOLUTION
Avatar of Snarf0001
Snarf0001
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You should use a foreign key constraint to avoid this situation.

SELECT  C.*
FROM    #tblData C
        LEFT JOIN #tblData P ON C.PARENT = P.ID
WHERE   P.ID IS NULL;

Open in new window

Avatar of Member_2_99151
Member_2_99151

ASKER

Perfect!!! Thanks :-)
Hope it is okay to split points equally as there was only 1 minute between posts!