Re: Retrieving sub trees and levels in a nested set
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(*)
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.