[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 750
  • Last Modified:

Get all parent nodes in one 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     -

Please help me.
0
jnj_web_solutions
Asked:
jnj_web_solutions
  • 3
1 Solution
 
ienaxxxCommented:
from what i understand you're using a nested set model on hierarchical data.


Are u using a model like that:

ROOT  AA    ROOT
   AA      AB    BA
   AA      AC    BA
   AA      AD    BA
   AA      AE    BA
ROOT  BA   ROOT
   BA     BB    CA
   BA     BC    CA
.....


??

... And, if i got the point, you're trying to get all PARENT NODES, one level UP from a child node. Am i right? ...
0
 
jnj_web_solutionsAuthor Commented:
S i am trying to get all parent nodes ,one level up from a child node.
0
 
ienaxxxCommented:
OK, then you can simply use:
SELECT SId FROM tree WHERE LeftN = (SELECT LeftN FROM tree WHERE  SId = ( SELECT LeftN FROM tree WHERE SId = 'AMSTURELI'))

And you get all the parent nodes one level UP from AMSTURELI.

HTH
Bye!
0
 
ienaxxxCommented:
Yeah, i tested it on a MySQL:

on that table:

CREATE TABLE IF NOT EXISTS `nestedset` (
  `SId` int(11) NOT NULL AUTO_INCREMENT,
  `LeftN` int(11) NOT NULL,
  `RightN` int(11) NOT NULL,
  `ext1` int(11) DEFAULT NULL,
  `ext2` int(11) DEFAULT NULL,
  `ext3` int(11) DEFAULT NULL,
  PRIMARY KEY (`SId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

INSERT INTO `nestedset` (`SId`, `LeftN`, `RightN`, `ext1`, `ext2`, `ext3`) VALUES
(1, 0, 2147483647, NULL, NULL, NULL),
(2, 1, 4, NULL, NULL, NULL),
(3, 1, 4, NULL, NULL, NULL),
(5, 2, 3, NULL, NULL, NULL),
(6, 2, 3, NULL, NULL, NULL),
(7, 2, 3, NULL, NULL, NULL),
(8, 2, 3, NULL, NULL, NULL);

Open in new window


The query:

SELECT SId FROM `nestedset` WHERE LeftN = (SELECT LeftN FROM nestedset WHERE SId = (SELECT LeftN FROM nestedSet WHERE SId = 7))

Open in new window


returns:
SId
=======
2
3

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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