Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Subqueries and Primary Keys

Re: Subqueries and Primary Keys

From: Dennis Webb <webbd_at_hoffman.army.mil>
Date: 10 Oct 2002 12:27:53 -0700
Message-ID: <73da0a84.0210101127.637e5274@posting.google.com>


Karsten Farell <kfarrell_at_medimpact.com> wrote in message news:<s5Zo9.1697$GS5.130356483_at_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_at_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). Received on Thu Oct 10 2002 - 14:27:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US