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

From: Dave Hammond <dh1760_at_gmail.com>
Date: 12 Oct 2006 12:06:26 -0700
Message-ID: <1160679986.440022.90670_at_m7g2000cwm.googlegroups.com>



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.
Received on Thu Oct 12 2006 - 21:06:26 CEST

Original text of this message