How to get all child nodes from a parent node

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
jnj_web_solutionsAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
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
 
jnj_web_solutionsAuthor Commented:
Thank u :-)
0
 
lwadwellCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.