• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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
0
James Atkin
Asked:
James Atkin
2 Solutions
 
Snarf0001Commented:
select a.*
from tblData a
left join tblData b on a.Parent = b.ID
where a.Parent != '-' and b.ID is null

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Try this:

Select T.ID
from tblData T
LEFT JOIN tblData U
on T.[Parent] = U.ID
where U.ID is null

Open in new window

0
 
ste5anSenior DeveloperCommented:
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

0
 
James AtkinSenior Principle Software EngineerAuthor Commented:
Perfect!!! Thanks :-)
Hope it is okay to split points equally as there was only 1 minute between posts!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now