• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 553
  • Last Modified:

How to get all parent nodes ,one level up from a child node.

If i select any id in LeftN or RightN means i will get their all parent nodes.

SELECT SId FROM `tree` where `LeftN`='AMSTURELI' OR `RightN`='AMSTURELI' OR `LeftN` IN (SELECT SId FROM `tree` where `LeftN`='AMSTURELI') OR `RightN` IN(SELECT SId FROM `tree` where `RightN`='AMSTURELI')

I am using  above query. But i got only 2 results (AMSHEPORE,AMSSURIWE).

But i want all linked nodes in particular id. When i select(AMSTURELI)  this id ,i want like this:
                                SId

                  1        AMSSURIWE
                  2      AMSHEPORE
                  3      AMSZEDIVE
                  4      AMSKIKEHU
                  5      AMSZACARE
                  6      AMSHUHOBI
                  7      AMSCEWIWI
                  8      AMSBUZUBA
                  9      AMSROXINI
                  10     AMSHUJOLU
                  11     -
tree.sql
0
jnj_web_solutions
Asked:
jnj_web_solutions
1 Solution
 
lwadwellCommented:
You want more than 'one level up' as the title suggests ... you want the parent, grand-parent, great grand-parent ... etc.
tree
Try
select Parent_ID
  from (select @SId := (select SId from `tree` WHERE `LeftN`=@SId OR `RightN`=@SId) as Parent_ID
          from `tree`,
               (select @SId:='AMSTURELI')d)v
 where Parent_ID is not null

Open in new window

0
 
jnj_web_solutionsAuthor Commented:
Thank u very much :-)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now