homepage Welcome to WebmasterWorld Guest from 54.196.181.109
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
[mySql] Retrieving columns without children
fm86




msg:4314744
 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




msg:4315143
 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




msg:4316596
 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