How to SELECT tree/part of tree ?

How to SELECT tree/part of tree ?

Post by Vasiliy Goncharenk » Sun, 23 Jan 2000 04:00:00



Help, please.

MSSQL 7

I have a Nested Set model of Tree :
RecID           int,
ParentRecID     int,
RecName varchar

How to SELECT tree/part of tree from this table in MSSQL ?

In ORACLE it's easy :
____________________
SELECT
  ParentRecID,
  RecID,
  RecName
FROM MyTable
CONNECT BY  ParentRecID = PRIOR RecID
START WITH  ParentRecID = 0
____________________

But MSSQL have not "CONNECT BY" + "START WITH".
How to do the same in MSSQL ?

Result should be something like :
ParID   ID      Name (any text)
----------      ---------       --------------------
0       1       TreeRoot        
1       2       Branch 1 Level 1Rec 1
1       3       Branch 1 Level 2Rec 1
3       4       Branch 1 Level 1Rec 2
1       5       Branch 1 Level 1Rec 2
3       6       Branch 1 Level 1Rec 2
3       7       Branch 2 Level 1Rec 1
3       8       Branch 2 Level 1Rec 1
3       9       Branch 2 Level 1Rec 1
3       9       Branch 2 Level 1Rec 1
...

Vasiliy Goncharenko.
___________________

 
 
 

How to SELECT tree/part of tree ?

Post by BPMargoli » Mon, 24 Jan 2000 04:00:00


Vasiliy,

You might check out the section "Expanding Hierarchies" in the SQL Server
7.0 Books Online.

If you really have a nested set model of trees, then you would not be using
an equivalent of the Oracle CONNECT BY, as the Oracle CONNECT BY does not
support the nested set model. If you want to see how to work with the nested
set model of trees, I'd recommend Joe Celko's "SQL for Smarties" which gives
a very full discussion of working with trees, and in particular the nested
set model. BTW, according to Joe Celko, the Oracle CONNECT BY is orders of
magnitude slower than straight SQL.


> Help, please.

> MSSQL 7

> I have a Nested Set model of Tree :
> RecID  int,
> ParentRecID int,
> RecName varchar

> How to SELECT tree/part of tree from this table in MSSQL ?

> In ORACLE it's easy :
> ____________________
> SELECT
>   ParentRecID,
>   RecID,
>   RecName
> FROM MyTable
> CONNECT BY  ParentRecID = PRIOR RecID
> START WITH  ParentRecID = 0
> ____________________

> But MSSQL have not "CONNECT BY" + "START WITH".
> How to do the same in MSSQL ?

> Result should be something like :
> ParID ID Name (any text)
> ---------- --------- --------------------
> 0 1 TreeRoot
> 1 2 Branch 1 Level 1Rec 1
> 1 3 Branch 1 Level 2Rec 1
> 3 4 Branch 1 Level 1Rec 2
> 1 5 Branch 1 Level 1Rec 2
> 3 6 Branch 1 Level 1Rec 2
> 3 7 Branch 2 Level 1Rec 1
> 3 8 Branch 2 Level 1Rec 1
> 3 9 Branch 2 Level 1Rec 1
> 3 9 Branch 2 Level 1Rec 1
> ...

> Vasiliy Goncharenko.
> ___________________



 
 
 

How to SELECT tree/part of tree ?

Post by Vasiliy Goncharenk » Mon, 24 Jan 2000 04:00:00


Thank you,
it's the solution I need.


>You might check out the section "Expanding Hierarchies" in the SQL Server
>7.0 Books Online.

>If you really have a nested set model of trees, then you would not be using
>an equivalent of the Oracle CONNECT BY, as the Oracle CONNECT BY does not
>support the nested set model. If you want to see how to work with the nested
>set model of trees, I'd recommend Joe Celko's "SQL for Smarties" which gives
>a very full discussion of working with trees, and in particular the nested
>set model. BTW, according to Joe Celko, the Oracle CONNECT BY is orders of
>magnitude slower than straight SQL.



>> Help, please.

>> MSSQL 7

>> I have a Nested Set model of Tree :
>> RecID  int,
>> ParentRecID int,
>> RecName varchar

>> How to SELECT tree/part of tree from this table in MSSQL ?

>> In ORACLE it's easy :
>> ____________________
>> SELECT
>>   ParentRecID,
>>   RecID,
>>   RecName
>> FROM MyTable
>> CONNECT BY  ParentRecID = PRIOR RecID
>> START WITH  ParentRecID = 0
>> ____________________

>> ...

Vasiliy Goncharenko.
___________________