Re: Challenging query: traverse a tree using SQL?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/01/16
Message-ID: <34bfdf36.15483009@www.sigov.si>#1/1

On 16 Jan 1998 20:35:24 GMT, lesliet_at_u.washington.edu (L. Tseng) wrote:

>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.

This is a very basic "hierarchical query" with a simple addition of the ORDER BY clause. The only tricky part in your example is the fact that the root row in your data is both the parent and the child to itself, which causes loop and Oracle raises an error if you don't handle it separately. I used a DECODE function to solve this. So here is the query returning what you wanted:

SQL> SELECT nodeid,level FROM test_table   2 CONNECT BY PRIOR nodeid =
  3 DECODE(nodeid, parentnodeid, NULL, parentnodeid)   4 START WITH nodeid = 4
  5 ORDER BY level, nodeid;

   NODEID LEVEL
--------- ---------

        4         2
        1         3
        2         3
        6         4

SQL> SELECT nodeid,level FROM test_table   2 CONNECT BY PRIOR nodeid =
  3 DECODE(nodeid, parentnodeid, NULL, parentnodeid)   4 START WITH nodeid = 3
  5 ORDER BY level, nodeid;

   NODEID LEVEL
--------- ---------

        3         1
        4         2
        8         2
        1         3
        2         3
        7         3
        9         3
        6         4
       10         4

9 rows selected.

>Leslie
>--
>=-=-=-=-=-=-=-=-=-=-=
>Leslie Tseng
>lesliet_at_writeme.com
>=-=-=-=-=-=-=-=-=-=-=

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Jan 16 1998 - 00:00:00 CST