I don't understand the results of my hierarchical query

From: kim <wykoffkb_at_yahoo.com>
Date: 20 Nov 2006 05:23:54 -0800
Message-ID: <1164029034.763176.256750@e3g2000cwe.googlegroups.com>

I have a table with nodes and an association table which stores the relationships between the nodes. I wanted to try using the hierarchical query on my tables, but I didn't get the results I expected. Here are my two tables:

SQL> select id from nodes;

ID

---

1
2
3
4
5
6
7
8

8 rows selected.

SQL> select parent, child from graph;

PARENT CHILD
------ -----

4      2
4      3
5      4
1      8
2      6
2      7

6 rows selected.

Now here is my query:
select RPAD(' ', 2*(LEVEL-1)) || ID ID, parent, LEVEL from nodes

    LEFT OUTER JOIN graph
    ON id = child
CONNECT BY PRIOR ID = parent;

ROWNUM ID PARENT LEVEL
------ ------- ------ ------

     1 8       1           1
     2 6       2           1
     3 7       2           1
     4 2       4           1
     5   6     2           2
     6   7     2           2
     7 3       4           1
     8 4       5           1
     9   2     4           2
    10     6   2           3
    11     7   2           3
    12   3     4           2
    13 1                   1
    14   8     1           2
    15 5                   1
    16   4     5           2
    17     2   4           3
    18       6 2           4
    19       7 2           4
    20     3   4           3

20 rows selected.

I expected to see rows 13-20, but not all of the rest. Is this normal?

Here are the results of just the join:
select RPAD(' ', 2*(LEVEL-1)) || ID ID, parent from nodes

    LEFT OUTER JOIN graph
    ON id = child;

ID PARENT
------- ------

2       4
3       4
4       5
8       1
6       2
7       2

1
5

I read that Oracle 9i has problems with the JOIN and CONNECT BY, but I tried it with the old Oracle outer join and I get the same results. I'm using 9.2.0.5.0.

thanks! Received on Mon Nov 20 2006 - 07:23:54 CST