Solved

How to get all child nodes from a parent node

Posted on 2012-09-07
Medium Priority
1,495 Views
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
Question by:jnj_web_solutions
• 2

LVL 25

Accepted Solution

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 <> ''
``````
0

Author Closing Comment

ID: 38378778
Thank u :-)
0

LVL 25

Expert Comment

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

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