Re: Connect By hierarchy problem

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

>

Oracle 9i and above:
SELECT
  PARENT_ID, SUBSTR(SYS_CONNECT_BY_PATH(CHILD_ID,',')||',',2,INSTR(SYS_CONNECT_BY_PATH(CHILD_ID,',')||',',',',1,2)-2) CHILD_ID,
  LEVEL
FROM
  RELATION
CONNECT BY PRIOR
  PARENT_ID=CHILD_ID; The SYS_CONNECT_BY_PATH option was located by a Google search on the terms: oracle connect by

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Oct 18 2006 - 14:42:20 CDT