Retrieving records from table A that are not in Table B

[johncnoland]

Table A is a master set of keys values (Date, ID, City).

The records in Table B represent a sub-set of the key values in Table A.

How can I retrieve the records in Table A that are not in Table B?

Thought of using a sub-query but don't those only return a single column?

Using SQL 2000.

 

[SQLDenis]

select a.* from tablea a
left join tableb b on a.Date = b.Date
and a.id = b.id
and a.city =b.city
where a.id is null

example

CREATE TABLE TestTable1 (id1 int) 
CREATE TABLE TestTable2 (id2 int) 

INSERT TestTable1 VALUES(1) 
INSERT TestTable1 VALUES(2) 
INSERT TestTable1 VALUES(3) 


INSERT TestTable2 VALUES(1) 
INSERT TestTable2 VALUES(2) 

SELECT t1.* 
FROM TestTable1 t1 
LEFT JOIN TestTable2 t2 ON t2.id2 = t1.id1 
WHERE t2.id2 IS NULL

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions