homepage Welcome to WebmasterWorld Guest from 54.237.99.131
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
[mySql] Retrieving columns without children
fm86

5+ Year Member



 
Msg#: 4314742 posted 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

5+ Year Member



 
Msg#: 4314742 posted 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

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



 
Msg#: 4314742 posted 11:36 pm on May 23, 2011 (gmt 0)

LEFT JOIN the alias where IS NULL.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved