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

[grasp06110]

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'

[Michel Cadot]

"grasp06110" <grasp...@yahoo.com> a écrit dans le message de news: 1181079752....@g4g2000hsf.googlegroups.com...

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

[grasp06110]

On Jun 6, 12:21 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "grasp06110" <grasp06...@yahoo.com> a écrit dans le message de news: 1181079752.933617.84...@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_thre...

Got It. Thanks for the help!