Welcome to WebmasterWorld Guest from 54.167.116.62

Forum Moderators: open

Message Too Old, No Replies

[mySql] Retrieving columns without children

     

fm86

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

5+ Year Member



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)

5+ Year Member



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)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



LEFT JOIN the alias where IS NULL.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month