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:

                  1        AMSSURIWE
                  2      AMSHEPORE
                  3      AMSZEDIVE
                  4      AMSKIKEHU
                  5      AMSZACARE
                  6      AMSHUHOBI
                  7      AMSCEWIWI
                  8      AMSBUZUBA
                  9      AMSROXINI
                  10     AMSHUJOLU
                  11     -
Who is Participating?
lwadwellConnect With a Mentor Commented:
You want more than 'one level up' as the title suggests ... you want the parent, grand-parent, great grand-parent ... etc.
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

jnj_web_solutionsAuthor Commented:
Thank u very much :-)
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.