Retrieving sub trees and levels in a nested set

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

Retrieving Data in XML Format

Hi,
I use nested loop with inner joins to retrieve organization's chart from
EmployeeID-ReportsTo columns.
Our client uses XML and I must send the chart in XML format. How can I
perform this?
Any help would be greatly appreciated.
Leila
Did you look at the details about FOR XML clause in SQL Server Books Online?
Anith
|||Thanks Anith,
But I couldn't use FOR XML for my problem. When I query the sub-employees of
a particular employee, I must keep that in XML format. Then each
sub-employee may have sub-employees itself that should be inserted beneath
the related element.
Maybe I'm using wrong algorithm?
Please help me..
Thanks in advance..
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uU#JCFr5EHA.3648@.TK2MSFTNGP11.phx.gbl...
> Did you look at the details about FOR XML clause in SQL Server Books
Online?
> --
> Anith
>
|||You will need to do this in your client tool. I suggest you post to a
newsgroup that supports whatever client application you are using.
Bob Barrows
Leila wrote:[vbcol=seagreen]
> Thanks Anith,
> But I couldn't use FOR XML for my problem. When I query the
> sub-employees of a particular employee, I must keep that in XML
> format. Then each sub-employee may have sub-employees itself that
> should be inserted beneath the related element.
> Maybe I'm using wrong algorithm?
> Please help me..
> Thanks in advance..
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uU#JCFr5EHA.3648@.TK2MSFTNGP11.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Retrieving Data in XML Format

Hi,
I use nested loop with inner joins to retrieve organization's chart from
EmployeeID-ReportsTo columns.
Our client uses XML and I must send the chart in XML format. How can I
perform this?
Any help would be greatly appreciated.
LeilaDid you look at the details about FOR XML clause in SQL Server Books Online?
--
Anith|||Thanks Anith,
But I couldn't use FOR XML for my problem. When I query the sub-employees of
a particular employee, I must keep that in XML format. Then each
sub-employee may have sub-employees itself that should be inserted beneath
the related element.
Maybe I'm using wrong algorithm?
Please help me..
Thanks in advance..
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uU#JCFr5EHA.3648@.TK2MSFTNGP11.phx.gbl...
> Did you look at the details about FOR XML clause in SQL Server Books
Online?
> --
> Anith
>|||You will need to do this in your client tool. I suggest you post to a
newsgroup that supports whatever client application you are using.
Bob Barrows
Leila wrote:
> Thanks Anith,
> But I couldn't use FOR XML for my problem. When I query the
> sub-employees of a particular employee, I must keep that in XML
> format. Then each sub-employee may have sub-employees itself that
> should be inserted beneath the related element.
> Maybe I'm using wrong algorithm?
> Please help me..
> Thanks in advance..
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uU#JCFr5EHA.3648@.TK2MSFTNGP11.phx.gbl...
>> Did you look at the details about FOR XML clause in SQL Server Books
>> Online?
>> --
>> Anith
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"