Subqueries and Primary Keys

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 Received on Wed Oct 09 2002 - 10:45:17 CDT