Re: Connect By hierarchy problem

On Oct 17, 12:14 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> Mike C wrote:
> > 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.How close does this come?
> SELECT
> PARENT_ID,
> CHILD_ID,
> LEVEL
> FROM
> RELATION
> CONNECT BY PRIOR
> PARENT_ID=CHILD_ID;
>
> PARENT_ID CHILD_ID LEVEL
> ========== ========== ==========
> 2 1 1
> 3 2 2
> 10 3 3
> 3 2 1
> 10 3 2
> 10 3 1
> 10 5 1
> 14 11 1
> 11 17 1
> 14 11 2
>
> SELECT
> PARENT_ID,
> CHILD_ID,
> LEVEL
> FROM
> RELATION
> CONNECT BY PRIOR
> CHILD_ID=PARENT_ID;
>
> PARENT_ID CHILD_ID LEVEL
> ========== ========== ==========
> 2 1 1
> 3 2 1
> 2 1 2
> 10 3 1
> 3 2 2
> 2 1 3
> 10 5 1
> 11 17 1
> 14 11 1
> 11 17 2
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

That is close, but if you look it lists child_id=2, parent_id=3 2X with a different level. Received on Tue Oct 17 2006 - 13:19:41 CDT