[ACCEPTED]-Find leaf nodes in hierarchical tree-tree

Accepted answer
Score: 22

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

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

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

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


Score: 1
Select * from mytree where id not in (Select distinct parentid from mytree where parentid is not null)



Score: 0
select *
from `mytree `
where not exists (select *
                  from `mytree ` as `nodes`
                  where `categories`.`id` = `nodes`.`parent`)


More Related questions