Re: Subqueries and Primary Keys

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