Re: query to trace all parents

And what database is in use? If you have Oracle then you can use the connect by clause to solve this:

UT1 > set echo on
UT1 > drop table pc;

Table dropped.

UT1 > create table pc ( parent varchar2(01), child varchar2(01) );

Table created.

UT1 > insert into pc values ('A','B');

1 row created.

UT1 > insert into pc values ('B','C');

1 row created.

UT1 > insert into pc values ('B','E');

1 row created.

UT1 > insert into pc values ('C','D');

1 row created.

UT1 > insert into pc values ('E','F');

1 row created.

UT1 > insert into pc values ('E','G');

1 row created.

UT1 >
UT1 > select parent
  2 from pc
  3 connect by child = prior parent
  4 start with child = 'C'
  5 /

P
-
B
A

UT1 > select parent
  2 from pc
  3 connect by child = prior parent
  4 start with child = 'F'
  5 /

P
-
E
B
A

UT1 > select parent
  2 from pc
  3 connect by child = prior parent
  4 start with child = 'B'
  5 /

P
-
A

Oracle even provides functions that work with connect by to show the full path etc...

SQL> l

  1 select sys_connect_by_path(ename,'_')   2 from emp
  3 connect by mgr = prior empno
  4* start with ename = 'KING'
SQL> / SYS_CONNECT_BY_PATH(ENAME,'_')

Don't have Oracle. Well Joe Celko wrote a whole book on dealing with hierarchies using SQL.

HTH -- Mark D Powell -- Received on Fri Jun 24 2005 - 15:13:55 CEST