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

"grasp06110" <grasp06110_at_yahoo.com> a écrit dans le message de news: 1181079752.933617.84300_at_g4g2000hsf.googlegroups.com...
| 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'
|

3 ways:

SQL> select
  2 *
  3 from
  4 tree
  5 where child != 'ROOT'
  6 start with
  7 child = 'ROOT'
  8 connect by
  9 parent = prior child
 10 /
PARENT CHILD
---------- ----------

ROOT       NODE1
ROOT       NODE2
ROOT       NODE3

3 rows selected.

SQL> select
  2 *
  3 from
  4 tree
  5 where level != 1
  6 start with
  7 child = 'ROOT'
  8 connect by
  9 parent = prior child
 10 /
PARENT CHILD
---------- ----------

ROOT       NODE1
ROOT       NODE2
ROOT       NODE3

3 rows selected.

SQL> select
  2 *
  3 from
  4 tree
  5 where child != 'ROOT'
  6 start with
  7 parent = 'ROOT'
  8 connect by
  9 parent = prior child
 10 /
PARENT CHILD
---------- ----------

ROOT       NODE1
ROOT       NODE2
ROOT       NODE3

3 rows selected.

Regards
Michel Cadot Received on Tue Jun 05 2007 - 23:21:58 CDT