Re: Connect By hierarchy problem

On Oct 17, 10:05 am, "Mike C" <michaelj..._at_hotmail.com> wrote:
> I have a simple table with a hierarchical structure:
>
> CREATE TABLE relation (parent_id number, child_id number);
>
> INSERT INTO relation values (10,3);
> INSERT INTO relation values (3,2);
> INSERT INTO relation values (2,1);
> INSERT INTO relation values (10,5);
> INSERT INTO relation values (14,11);
> INSERT INTO relation values (11,17);
>
> I need to a query to return a results that lists every child and it's
> direct and not direct parent (it's parents parent and so on), Having
> the level would be nice too For example, I want this back:
>
> Parent_id Child_id
>
> 10 3
> 3 2
> 10 5
> 14 11
> 11 17
> 10 2
> 14 17
> 2 1
> 3 1
> 10 1
>
> I can't seem to get CONNECT BY to do this.

What I have so far is this:

SELECT * FROM (
select child_id , (select parent_id from relation r2 where level=2
start with r1.child_id= r2.child_id
connect by prior parent_id=child_id) parent_id, 2 lvl FROM relation r1
UNION
select child_id , (select parent_id from relation r2 where level=1
start with r1.child_id= r2.child_id
connect by prior parent_id=child_id) parent_id, 1 lvl FROM relation r1
) where parent_id is not null

The problem is that it only handles 2 levels and there really could be an inifinte number of levels. Received on Tue Oct 17 2006 - 11:50:18 CDT