Path: news.f.de.plusline.net!news-fra1.dfn.de!news.tele.dk!feed118.news.tele.dk!postnews.google.com!g4g2000hsf.googlegroups.com!not-for-mail From: grasp06110 Newsgroups: comp.databases.oracle.misc Subject: Re: start with parent but don't include parent in results Date: Wed, 06 Jun 2007 17:21:23 -0700 Organization: http://groups.google.com Lines: 146 Message-ID: <1181175683.316954.33790@g4g2000hsf.googlegroups.com> References: <1181079752.933617.84300@g4g2000hsf.googlegroups.com> <46663666$0$1147$426a74cc@news.free.fr> NNTP-Posting-Host: 75.21.54.206 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1181175683 2639 127.0.0.1 (7 Jun 2007 00:21:23 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Thu, 7 Jun 2007 00:21:23 +0000 (UTC) In-Reply-To: <46663666$0$1147$426a74cc@news.free.fr> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.12) Gecko/20070508 Firefox/1.5.0.12,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: g4g2000hsf.googlegroups.com; posting-host=75.21.54.206; posting-account=_inzqg0AAADDTAM-mX2WJqdM9jruqGED Xref: news.f.de.plusline.net comp.databases.oracle.misc:79877 On Jun 6, 12:21 am, "Michel Cadot" wrote: > "grasp06110" a =E9crit dans le message de news: 11= 81079752.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... > | > | 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 =3D 'ROOT' > | connect by > | parent =3D prior child > | > | /* gives all records */ > | select > | * > | from > | tree > | start with > | child =3D 'ROOT' > | connect by > | parent =3D prior child > | and child !=3D 'ROOT' > | > | /* gives no records */ > | select > | * > | from > | tree > | start with > | child =3D 'ROOT' > | and child !=3D 'ROOT' > | connect by > | parent =3D 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 =3D 'ROOT' > | connect by > | parent =3D prior child > | ) > | where child !=3D 'ROOT' > | > > 3 ways: > > SQL> select > 2 * > 3 from > 4 tree > 5 where child !=3D 'ROOT' > 6 start with > 7 child =3D 'ROOT' > 8 connect by > 9 parent =3D prior child > 10 / > PARENT CHILD > ---------- ---------- > ROOT NODE1 > ROOT NODE2 > ROOT NODE3 > > 3 rows selected. > > SQL> select > 2 * > 3 from > 4 tree > 5 where level !=3D 1 > 6 start with > 7 child =3D 'ROOT' > 8 connect by > 9 parent =3D prior child > 10 / > PARENT CHILD > ---------- ---------- > ROOT NODE1 > ROOT NODE2 > ROOT NODE3 > > 3 rows selected. > > SQL> select > 2 * > 3 from > 4 tree > 5 where child !=3D 'ROOT' > 6 start with > 7 parent =3D 'ROOT' > 8 connect by > 9 parent =3D prior child > 10 / > PARENT CHILD > ---------- ---------- > ROOT NODE1 > ROOT NODE2 > ROOT NODE3 > > 3 rows selected. > > Regards > Michel Cadot Got It. Thanks for the help!