| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with multi-table partial select
A copy of this was sent to danb5_at_my-dejanews.com
(if that email address didn't require changing)
On Tue, 21 Jul 1998 16:32:24 GMT, you wrote:
>I am trying to formulate a query involving two tables, and I'm not having much
>luck.
>
>The schema: One table is "family" while the other is "person". Each person
>has a foreign key to a family. For instance, Let's say ID 42 is assigned to
>family "Clinton" and you have person "Bill" which holds the foreign key 42 to
>the Clinton family.
>
>What I'm trying to do is to formulate a query to choose one and only one
>person per family. (It doesn't particularly matter which one; perhaps the
>first one found according to sort order). The closest I can get is this
>query to choose _every_ family member:
>
>select p.id, f.id, p.firstname, f.name
>from person p, family f
>where f.id=p.familyid
>order by f.name
>
if you don't care which one from family comes back it is simple to do:
SQL> create table family ( id int primary key, name varchar2(255) ); Table created.
SQL> create table person( id int primary key,
2 familyid int references family(id),
3 firstname varchar2(255) );
Table created.
SQL> insert into family values ( 42, 'Clinton' ); SQL> insert into person values ( 1, 42, 'Bill' ); SQL> insert into person values ( 2, 42, 'Hillary' ); SQL> insert into person values ( 3, 42, 'Chelsea' );
SQL> l
1 select f.id, f.name, p.id, p.firstname
2 from family f, person p
3 where p.rowid = ( select max(rowid)
4 from person p2 5 where p2.familyid = f.id )6* order by f.name
ID NAME ID FIRSTNAME
---------- ------------------------------ ---------- ---------
42 Clinton 3 Chelsea
The correlated subquery will get a rowid for the current family record, a semi random rowid -- it won't always be the last inserted row, it will be some rowid...
>select p.id, f.id, p.firstname, f.name
>from person p, family f
>where f.id=p.familyid
>order by f.name
>I know I could just use this select statement and programatically throw away
>extraneous family members, but I really need to do this in one query.
>Perhaps a nested select or some such?
>
>So far, all my SQL-knowledgeable friends are stumped. Any ideas? TIA.
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jul 21 1998 - 13:53:56 CDT
 |
 |