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

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.

Always include DDL because keys and constraints make a difference - that's what they are for. Here's my guess:

SELECT NAME, ID, DATE
FROM A
WHERE NOT EXISTS
(SELECT 1
 FROM B
 WHERE A.NAME = B.NAME
 AND A.DATE = B.DATE);

-- 
David Portas