Given a table with the following structure,

ItemID    Integer (primary key),
ParentID    Integer (foreign key to ItemID),
Caption    String

where ParentID may point to a parent ItemID,

what is the best way to retrieve all the possible levels of children for a
given ItemID?

I can't think of a single SQL statement that will do this. I can imagine
writing a recursive stored proc that creates dynamic SQL statements and
global temporary tables.

Any help appreciated.

TIA,

Mike

As always - this is dependent on your SQL flavour, I develop on Oracle
(8 onwards), I really don't know about any other flavours. This is good
for Oracle 8.x

create table testparent (
   itemid   number(4),
   parentid number(12),
   caption  varchar2(20)
   );
/
insert into testparent values( 1,null,'Root Object' );
insert into testparent values( 10,1,'First Level' );
insert into testparent values( 20,1,'First Level' );
insert into testparent values( 100,10,'Second Level' );
insert into testparent values( 200,10,'Second Level' );
insert into testparent values( 300,20,'Second Level' );
insert into testparent values( 400,20,'Second Level' );
/
select itemid, parentid, caption, level
from   testparent
start with itemid = 1
connect by prior itemid = parentid

ItemID  ParentID        Caption Level
=====   ======  ======= =====
1                       Root Object     1
10      1               First Level     2
100     10              Second Level    3
200     10              Second Level    3
20      1               First Level     2
300     20              Second Level    3
400     20              Second Level    3

Regards
Bill

In message <3d3dc7c2$1_2@dnews>, Michael J. Austin <austi...@ohsu.edu>
writes

SELECT p.ItemID, i.ItemID, i.Caption
  FROM table i
  LEFT OUTER JOIN table p ON i.ParentID = p.ItemID
 ORDER BY i.ItemID

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
"Some see private enterprise as a predatory target to be shot, others as a
cow to be milked, but few are those who see it as a sturdy horse pulling the
wagon." - Winston Churchill

What's your DB?

rb

"Michael J. Austin" <austi...@ohsu.edu> wrote in message
news:3d3dc7c2$1_2@dnews...