Re: Connect By hierarchy problem

From: Mike C <michaeljc70_at_hotmail.com>
Date: 18 Oct 2006 08:24:27 -0700
Message-ID: <1161185066.968266.274600@b28g2000cwb.googlegroups.com>

Charles Hooper wrote:
> Mike C wrote:
> > 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.

>

> The reason that you are seeing the repeated values at a different level
> is because Oracle was not told what value to use as starting point of
> the hierarcy - it runs through all of the possibilities. In the first
> SQL statement, the first child_id=2, parent_id=3 was found by first
> running through the child_id=1, parent_id=2 match, and then it found
> that it could connect to child_id=2, parent_id=3 from there. The
> second time this match appeared, it immediately found the child_id=2,
> parent_id=3 match. What this requires is the program which reads the
> output to generate new output that looks for the level to be the same
> or less than the previous level to decide that a new child record has
> been found. For example, using the output of the first SQL statement:
> Read in P=2, C=1, L=1 - P=2, C=1
> Read in P=3, C=2, L=2 - this is still part of the same connect by
> chain, P=3, C=1 holds
> Read in P=10, C=3, L=3 - this is still part of the same connect by
> chain, P=10, C=1 holds
> Read in P=3, C=2, L=1 - this is the start of a new chain, P=3, C=2
> Read in P=10, C=3, L=3 - this is still part of the same connect by
> chain, P=10, C=2 holds
> ...

>

> And then you need to obtain the distinct list of parent, child matches.
> The above can be done using PL/SQL.

>

> Let's look at this using another method, using a likely less efficient
> method using a self join:
> SELECT
> R1.CHILD_ID,
> R1.PARENT_ID P1,
> R2.PARENT_ID P2,
> R3.PARENT_ID P3,
> R4.PARENT_ID P4,
> R5.PARENT_ID P5
> FROM
> RELATION R1,
> RELATION R2,
> RELATION R3,
> RELATION R4,
> RELATION R5
> WHERE
> R1.PARENT_ID=R2.CHILD_ID(+)
> AND R2.PARENT_ID=R3.CHILD_ID(+)
> AND R3.PARENT_ID=R4.CHILD_ID(+)
> AND R4.PARENT_ID=R5.CHILD_ID(+);

>

> This outputs:
> CHILD_ID P1 P2 P3 P4 P5
> 1 2 3 10
> 3 10
> 5 10
> 11 14
> 17 11 14
> 2 3 10

>

> >From the above we can see that:
> CHILD_ID 1 has parents 2, 3, and 10
> CHILD_ID 3 has parents 10
> CHILD_ID 5 has parents 10
> CHILD_ID 11 has parents 14
> CHILD_ID 17 has parents 11, and 14
> CHILD_ID 2 has parents 3, and 10

>

> Using the above, it is easy to determine the levels for the parents.

>

> There might be a CONNECT BY syntax that gives you what you want without
> multiple self joins, and without having to create a program to
> interpret the results of the CONNECT BY clause.

>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

That is the essence of the problem- it seems the STARTS WITH clause can't be used. The problem is seemingly simple, but it is not. People just don't take the time to read and think, they would rather criticise. Thanks for your effort. Received on Wed Oct 18 2006 - 10:24:27 CDT