Re: start with parent but don't include parent in results
- From: grasp06110 <grasp06110@xxxxxxxxx>
- Date: Wed, 06 Jun 2007 17:21:23 -0700
On Jun 6, 12:21 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
"grasp06110" <grasp06...@xxxxxxxxx> a écrit dans le message de news: 1181079752.933617.84...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| 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!
.
- References:
- tree: start with parent but don't include parent in results
- From: grasp06110
- Re: start with parent but don't include parent in results
- From: Michel Cadot
- tree: start with parent but don't include parent in results
- Prev by Date: "Not in" subquery
- Next by Date: join and trees
- Previous by thread: Re: start with parent but don't include parent in results
- Next by thread: SQL statement to query all fields from a table
- Index(es):
Relevant Pages
|
