Re: Subqueries and Primary Keys

webbd_at_hoffman.army.mil (Dennis Webb) wrote in message news:<73da0a84.0210090745.502aca34_at_posting.google.com>...
> 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?
>

Check your where clause and subqueries. Size of the resultset shouldn't be dependent on the select clause. But... What version of Oracle is it? There are several bad bugs in 8.1.6, nothing as bad as you describe though.

> 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 Thu Oct 10 2002 - 11:34:00 CDT