Re: Connect By hierarchy problem

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. Received on Tue Oct 17 2006 - 12:14:43 CDT