asked on

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.

ASKER CERTIFIED SOLUTION

ienaxxx

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jnj_web_solutions

ASKER

S i am trying to get all parent nodes ,one level up from a child node.

ienaxxx

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!

ienaxxx

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