Hello,

I have a nested set hierarchy set up that looks a lot like this.

TABLE
Id, lft, rgt
A, 1, 14
B, 2, 3
C 4, 13
D 5, 6
E 7, 10
F 8, 9
G 11, 12

I want to select specific nodes and return its parents like this.

SELECT one.*
FROM table AS one, table AS two
WHERE two.lft BETWEEN one.lft AND one.rgt
AND one.id IN (‘D’, ‘E’, ‘B’)

Returns something like:

A, 1, 14
B, 2, 3
C, 3, 13
D, 5, 6
E, 7, 8

But not F or G because they are not above D, E, or B.

But I also want to know what level each of the nodes in the hierarchy is on. For example:

A, 0
B, 1
C, 1
D, 2
E, 2

Similar to what the following query returns only limited to the sub trees I select.

SELECT count(two.id) AS level, one.id
FROM table AS one, table AS two
WHERE one.lft BETWEEN two.lft AND two.rgt
GROUP BY one.id

So, what I really want to do is combine these two queries so that I can select multiple sub trees and know what each of the levels of hierarchy are.

Any help is greatly appreciated!
-Rob

This is what I did...it's not very elegant so if anyone has more suggestions please let me know.

SELECT two.id, (SELECT count(*)
FROM table
WHERE lft <= two.lft
AND rgt >= two.rgt) AS level
FROM table AS one, table AS two
WHERE one.lft BETWEEN two.lft and two.rgt
AND one.id IN ('D', 'E', 'B')
GROUP BY two.id, two.lft, two.rgt
ORDER BY two.lft

It takes a bit more time than I had desired but it works for now. I am still researching other options so please feel free to reply to this message.

-Rob