Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Query Treewalk Teaser
The trouble with connect by is that it only traverses the tree up or down,
not the whole tree.
Say I have the following scenario:
ID1 ID2 | | -------- | ID3 | -------- | | ID4 ID5 |
I have created this with the following test case:
SQL> desc tree
Name Null? Type ------------------------------- -------- ---- ID NUMBER LINK_ID NUMBER
SQL> select * from tree
2 /
ID LINK_ID
--------- ---------
1 3 2 3 3 4 3 5 4 6 5 6
Now say my starting ID is ID4, I can get the values up the tree by doing the following:
SQL> l
1 select level, ID
2 from tree
3 connect by prior ID = link_Id
4* start with ID = 4
SQL> /
LEVEL ID
--------- ---------
1 4 2 3 3 1 3 2
And I can get the values down the tree by doing this...
SQL> select level, ID
2 from tree
3 connect by ID = prior link_Id
4 start with ID = 4
5 /
LEVEL ID
--------- ---------
1 4 2 6
But if I want to get ALL values in the tree, how do I do it?
I tried the following query which should have fired off the downward tree walk for each value up the tree, but alas to no avail...
1 select level, ID
2 from tree
3 connect by ID = prior link_Id
4 start with ID = (select ID from tree
5 connect by prior ID = link_Id 6* start with id = 4)7 /
*
I'd really appreciate some help
Regards,
Mike Cretan
"Ujjwalc" <ujjwalc_at_aol.com> wrote in message news:20020429014638.24174.00006310_at_mb-mi.aol.com...
> Have you aready considered the > "connect by" ? > > Put some sample data , tables and the > output you woud like -- may be > somene will reply. > > Regards >Received on Mon Apr 29 2002 - 19:06:48 CDT