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