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

Posted on 2012-09-06
Last Modified: 2012-09-07
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     -
Question by:jnj_web_solutions
    LVL 25

    Accepted Solution

    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


    Author Closing Comment

    Thank u very much :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    This video discusses moving either the default database or any database to a new volume.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now