Re: Finding rows in table A which do not have a match in table B

From: Bob Stearns <rstearns1241_at_charter.net>
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