I've got a table during my database which stores a tree structure. Listed here are the appropriate fields:

mytree (id, parentid, otherfields...)

I wish to find all of the leaf nodes (that's, any record whose id isn't another record's parentid)

I have attempted this:

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

But that came back a clear set. Oddly, getting rid of the "NOT" returns the set of all of the non-leaf nodes.

Can anybody see where I am failing?

Update: Just solutions men, every one has been correct and labored for me personally. I have recognized Daniel's because it also describes why my query did not work (the NULL factor).

Your query did not work since the sub-query includes NULL. The next slight modification works best for me:

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

Not a clue why your query did not work. Here's exactly the same factor in left outer join syntax - check it out by doing this?

select a.*
from mytree a left outer join
     mytree b on a.id = b.parentid
where b.parentid is null
SELECT * FROM mytree AS t1
LEFT JOIN mytree AS t2 ON t1.id=t2.parentid
WHERE t2.parentid IS NULL

Choose * from mytree where id not in (Choose distinct parentid from mytree where parentid isn't null)

http://archives.postgresql.org/pgsql-sql/2005-10/msg00228.php