Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed.news2me.com!canoe.uoregon.edu!logbridge.uoregon.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: webbd@hoffman.army.mil (Dennis Webb)
Newsgroups: comp.databases.oracle.misc
Subject: Re: Subqueries and Primary Keys
Date: 10 Oct 2002 12:27:53 -0700
Organization: http://groups.google.com/
Lines: 55
Message-ID: <73da0a84.0210101127.637e5274@posting.google.com>
References: <73da0a84.0210090745.502aca34@posting.google.com> <s5Zo9.1697$GS5.130356483@newssvr21.news.prodigy.com>
NNTP-Posting-Host: 199.208.22.18
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1034278073 15398 127.0.0.1 (10 Oct 2002 19:27:53 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 10 Oct 2002 19:27:53 GMT
Xref: newsfeed1.easynews.com comp.databases.oracle.misc:87681
X-Received-Date: Thu, 10 Oct 2002 12:27:48 MST (news.easynews.com)

Karsten Farell <kfarrell@medimpact.com> wrote in message news:<s5Zo9.1697$GS5.130356483@newssvr21.news.prodigy.com>...
> Dennis Webb wrote:
> > I am getting unexpected results with an SQL script that uses two
> > subqueries.  It is of the general form below:
> > 
> > SELECT TABLE_A.ssn, TABLE_A.date
> > FROM TABLE_A
> > WHERE (TABLE_A.ssn = "123456789" and TABLE_A.date =
> >            (SELECT TABLE_B.date
> >             FROM TABLE_B
> >             WHERE TABLE_B.ssn = "123456789")  OR
> >        TABLE_A.ssn = "123456789" and TABLE_A.date =
> >            (SELECT TABLE_C.date
> >             FROM TABLE_C
> >             WHERE TABLE_C.ssn = "123456789"))
> > 
> > Tables A, B, and C all have ssn + date as their primary key.  I want
> > to retrieve a list of rows from table A, each row of which has a
> > corresponding row on either table B or table C.  The above query DOES
> > work and returns the correct rows, PROVIDED that the only columns
> > included in the main SELECT clause are the two primary key columns
> > "ssn" and "date".  If I include any additional columns in the SELECT
> > clause (e.g., TABLE_A.name or TABLE_B.age or TABLE_C.sex), then the
> > query returns what appears to be EVERY ROW in table A.
> > 
> > I don't understand why I am getting a return of all table A rows when
> > I include non-key columns in the SELECT clause.  Can someone enlighten
> > me about this?
> > 
> > Also, is there some way I can get this SQL to return the desired
> > matched rows, including the various columns I need from all three
> > tables?
> > 
> > Thanks for any help in this.
> > 
> > -Dennis Webb
> > webbd@hoffman.army.mil
> 
> Perhaps you could reformat your query to something like:
> 
> SELECT TABLE_A.ssn, TABLE_A.date,
>         TABLE_A.name, TABLE_B.age, TABLE_C.sex
> FROM   TABLE_A, TABLE_B, TABLE_C
> WHERE  TABLE_A.ssn = '123456789'
> AND    TABLE_B.ssn = TABLE_A.ssn
> AND    TABLE_C.ssn = TABLE_A.ssn
> AND    TABLE_B.date = TABLE_A.date
> AND    TABLE_C.date = TABLE_A.date


I've tried doing simple joins, of the sort you suggest, but it still
doesn't give me the correct rows.  Instead, I still get a row
representing every row on Table_C.  But you will notice I have an OR
in my SQL, since I want a matching record from Table_B OR from Table_C
(one of the two matching Table_A).