Alex Rivera | Logout

Find leaf nodes in hierarchical tree

Asked 2008-10-15T00:20:41.107
19

I have a table in my database which stores a tree structure. Here are the relevant fields:

mytree (id, parentid, otherfields...)

I want to find all the leaf nodes (that is, any record whose id is not another record's parentid)

I've tried this:

SELECT * FROM mytree WHERE `id` NOT IN (SELECT DISTINCT `parentid` FROM `mytree`)

But that returned an empty set. Strangely, removing the "NOT" returns the set of all the non-leaf nodes.

Can anyone see where I'm going wrong?

Update: Thanks for the answers folks, they all have been correct and worked for me. I've accepted Daniel's since it also explains why my query didn't work (the NULL thing).

sql database tree
Edit
Report

1 Answer

8

No clue why your query didn't work. Here's the identical thing in left outer join syntax - try it this way?

select a.*
from mytree a left outer join
     mytree b on a.id = b.parentid
where b.parentid is null
answered 2008-10-15T00:28:33.167

Your Answer