On Jun 6, 12:21 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "grasp06110" <grasp06..._at_yahoo.com> a écrit dans le message de news: 1181079752.933617.84..._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_thre...
> |
> | 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
Got It. Thanks for the help!
Received on Wed Jun 06 2007 - 19:21:23 CDT
