Re: Challenging query: traverse a tree using SQL?

From: (wrong string) ørgensen <kjorg_at_msn*DOT*com>
Date: 1998/01/16
Message-ID: <#HKptbtI9GA.225_at_upnetnews04>#1/1


Easy.

Select-connect-by will travel the tree for you:

    SELECT level, nodeid
    FROM mytable
    CONNECT BY PRIOR nodeid = parentnodeid     START WITH nodeid = 1

The "level" column is a pseudocolumn you can reference in select-connect-by statements.

Hope this helps

--
Karl
Oracle Certified DBA
L. Tseng wrote in message <69ogac$eau$1_at_nntp6.u.washington.edu>...

>Hi, SQL experts,
>
>does anyone know if traversing a tree can be done using sql?
>For example:
>
> nodeid parentnodeid level
>
> 3 3 1
> 4 3 2
> 8 3 2
> 2 4 3
> 1 4 3
> 9 8 3
> 7 8 3
> 6 2 4
> 10 7 4
>
>Tree structure:
> 3
> 4 8
> 2 1 9 7
> 6 10
>
>Question:
>Given a node id=4, how can I get a list of child node ids including itself
>order by level?
> 4
> 1
> 2
> 6
>
>Any help will be greatly appreciated.
>
>Leslie
>--
>=-=-=-=-=-=-=-=-=-=-=
>Leslie Tseng
>lesliet_at_writeme.com
>=-=-=-=-=-=-=-=-=-=-=
Received on Fri Jan 16 1998 - 00:00:00 CET

Original text of this message