?
Solved

How to get all child nodes from a parent node

Posted on 2012-09-07
3
Medium Priority
?
1,495 Views
Last Modified: 2012-09-07
I want the parent node,child node,grand child node,grand child node, etc....


SELECT `LeftN`, `RightN` FROM tree WHERE `LeftN`= 'AMSXATUMI' OR `RightN`='AMSXATUMI' OR `SId`= 'AMSXATUMI' OR `SId` IN (SELECT `LeftN` FROM tree WHERE `SId` = 'AMSXATUMI') OR `SId` IN (SELECT `RightN` FROM tree WHERE `SId` = 'AMSXATUMI')

I am using this above query. But i got some nodes only like

                                    AMSXATUMI
                                     /               \
                                                   AMSKEGUSU
                                                   /                 \
                                           AMSHUZEDU    AMSWIWEDE

I want all nodes like

                                    AMSXATUMI
                                     /               \
                                                   AMSKEGUSU
                                                   /                 \
                                   AMSHUZEDU             AMSWIWEDE
                                  /                  \               /                  \
                 AMSGAFOVU      AMSBUCEVO AMSLUHERE     AMSDENIBA
                 /               \             /            \           /   \             /          \  
 AMSNUFEBE    AMSVASETE AMSBOHOKA etc etc  etc       etc..     etc...
tree.sql
0
Comment
Question by:jnj_web_solutions
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38378772
try this
select leftn as SId
from amount
inner join (select @sid:=(select group_concat(leftn,',',rightn)
                          from amount
                          where find_in_set(sid, @nsid) >= 1)
                 , @nsid:=@sid as fset
            from amount, (select @nsid:='AMSXATUMI',@sid='')v )gr
        on find_in_set(leftn,fset)
where leftn <> ''
union
select rightn as SId
from amount
inner join (select @sid:=(select group_concat(leftn,',',rightn) 
                          from amount
                          where find_in_set(sid, @nsid) >= 1)
                 , @nsid:=@sid as fset
            from amount, (select @nsid:='AMSXATUMI',@sid='')v )gr
        on find_in_set(rightn,fset)
where rightn <> ''

Open in new window

0
 

Author Closing Comment

by:jnj_web_solutions
ID: 38378778
Thank u :-)
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38378791
BTW ... you are allowed to add extra information in the a question if an expert asks for more information or clarifications.  It is not necessary to accept that and start a new question.  I say this as I was about to post my answer in the earlier question when I found you had already accepted a answer.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 7 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question