tree: start with parent but don't include parent in results

Hi Everybody,

Trying to get all of the child records for a node in a tree without getting a record for the parent record and can't seem to do it properly.

This is similar to the posting at the url below but the parallel to the solution posted there doesn't seem to work here.

http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/e871fb4229fcd036/0ebb8a17606f4359?lnk=gst&q=%22start+with%22+%22connect+by%22+exclude+parent&rnum=1#0ebb8a17606f4359

Any help would be greatly appreciated.

Thanks,
John

Details below:

/* Currently working with Oracle 9i */

create table tree (
  parent varchar2(10),
  child varchar2(10),
  constraint tree_pk primary key(child),   constraint tree_child_fk foreign key(parent) references tree(child) );

insert into tree values (null, 'ROOT');
insert into tree values ('ROOT', 'NODE1');
insert into tree values ('ROOT', 'NODE2');
insert into tree values ('ROOT', 'NODE3');

select
*

from
  tree
start with
  child = 'ROOT'
connect by
  parent = prior child

/* gives all records */
select
*

from
  tree
start with
  child = 'ROOT'
connect by
  parent = prior child
  and child != 'ROOT'

/* gives no records */
select
*

from
  tree
start with
  child = 'ROOT'
  and child != 'ROOT'
connect by
  parent = prior child

/*
 * desired results but ugly,
 * especially if I need to combine
 *  this type of query with other queries
 */

select * from (
select
*

from
  tree
start with
  child = 'ROOT'
connect by
  parent = prior child
)
where child != 'ROOT' Received on Tue Jun 05 2007 - 16:42:32 CDT