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!
-RobThis 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
No comments:
Post a Comment