Re: SQL Query Treewalk Teaser

Got it!!

I was pretty close. Just have to change the last query of mine to use "in" rather than =

ie
  1 select distinct ID
  2 from tree
  3 connect by ID = prior link_Id
  4 start with ID in
  5 (select ID from tree
  6 connect by prior ID = link_Id
  7* start with id = 4)
SQL> /        ID

        1
        2
        3
        4
        5
        6

Thanks for helping me clarify my thinking ;)

Mike

"Mike Cretan" <mcretan_at_ozemail.com.au> wrote in message news:Oclz8.14$f%.11565_at_vicpull1.telstra.net...
> 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
> |
> ID6
>
> 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 /
> start with ID = (select ID from tree
> *
> ERROR at line 4:
> ORA-01427: single-row subquery returns more than one row
>
> 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 - 20:44:27 CDT