Re: Finding rows in table A which do not have a match in table B
Date: Thu, 12 Oct 2006 15:29:59 -0400
Message-ID: <ZIwXg.1720$kb2.1527_at_newsfe03.lga>
Dave Hammond wrote:
> Hi All,
>
> Given two tables A and B, both containing a column X with identical
> data, I'm trying to formulate a query which will return all rows from
> table A where there are no rows in table B with a matching value in
> column X.
>
> For example, assuming the tables each contain NAME and DATE columns:
>
> Table A looks like:
>
> NAME ID DATE
> ---- -- ----
> JOHN 01 2006-10-12
> JANE 02 2006-10-12
> FRED 03 2006-10-12
>
> Table B looks like:
>
> NAME HOURS DATE
> ---- ----- ----
> JOHN 40 2006-10-12
> JANE 40 2006-10-12
> FRED 25 2006-09-30
>
> The rows for JOHN and JANE have identical values in the DATE field of
> both tables; however, the row for FRED has a different DATE value in
> each table, so it would appear in the query result:
>
> NAME ID DATE
> ---- -- ----
> FRED 03 2006-09-30
>
>
> This is probably a newbie SQL question, but I can't find the solution,
> so I'm asking the experts. :)
> Thanks!
>
> -Dave H.
>
If I've correctly divined the ddl, in db2 a solution is:
select t1.name, t1.id, t2.date
from tablea t1 join tableb t2 on name where (t1.name, t1.date) not in (select name,date from tableb)
If you're not using db2 luw v8.1.9+ or I haven't divined the correct table structures or contents then all bets are off! Received on Thu Oct 12 2006 - 21:29:59 CEST