Michel Cadot wrote:
> "Mike C" <michaeljc70_at_hotmail.com> a écrit dans le message de news: 1161267324.784629.195520_at_k70g2000cwa.googlegroups.com...
> |
> | Charles Hooper wrote:
> | > 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.
> | > >
> | > > 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.
> | >
> | > 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.
> |
> | Thanks- that is it!
> |
>
> And that is in the first answer I gave you!
> And that is basic!
> Do you see anything complex in there, Mr "I have done SQL for 12 years"?
>
> Regards
> Michel Cadot
SUBSTR(SYS_CONNECT_BY_PATH(CHILD_ID,',')||',',2,INSTR(SYS_CONNECT_BY_PATH(CHILD_ID,',')||',',',',1,2)-2)
is basic?
SYS_CONNECT_BY_PATH is new to Oracle 9 smart ass and hasn't been around
for 12 years. You are just an ass! I am glad I don't have to work with
someone like you.
SYS_BY_CONNECT
Received on Thu Oct 19 2006 - 17:49:45 CDT
