Find leaf nodes in hierarchical tree

database, sql, tree

Solution

Your query didn't work because the sub-query includes `NULL`. The following slight modification works for me:

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

Problem

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).

Original source