[mySql] Retrieving columns without children

Forum Moderators: open

Message Too Old, No Replies

[mySql] Retrieving columns without children

fm86

4:02 pm on May 19, 2011 (gmt 0)

Hi everybody!

I am storing a tree in a mySql DB. Essentially I have 2 colums: id and parentId

For example:


 id  parentId
 1    0     //root 
 2    1
 3    1
 4    2
 5    3

Is there an efficient query to get only those rows that have no children (in this case row with ids 4 and 5)

fm86

8:16 am on May 20, 2011 (gmt 0)

I tried

SELECT distinct t1.id FROM
clouds AS t1 LEFT JOIN clouds as t2
ON t1.id <> t2.parent

where clouds is the name of the table
but it returns all the rows... On the other way:

SELECT distinct t1.id FROM clouds AS t1 LEFT JOIN clouds as t2 ON t1.id = t2.parent

Returns the rows that are not leaves...
Any suggestion?

coopster

11:36 pm on May 23, 2011 (gmt 0)

LEFT JOIN the alias where IS NULL.