SQL: Select rows that are not present in table 2

[mkingrey]

I have Table1 with FieldA and Table2 with FieldA. I would like to query Table1 for all records that DO NOT exist in FieldA of Table2. I have tried the following but it doesn't filter correctly. Any suggestions?? Thx!!

SELECT Table1.FieldA FROM Table1, Table2 WHERE Table1.FieldA <> Table2.Field

 

[r937]

SELECT Table1.FieldA
FROM Table1 LEFT OUTER JOIN Table2
ON Table1.FieldA = Table2.Field
WHERE Table2.Field is null

r937.com | rudy.ca

 

[PHV]

either:
SELECT * FROM Table1
WHERE FieldA NOT IN (SELECT FieldA FROM Table2)

or:
SELECT A.*
FROM Table1 A LEFT JOIN Table2 B ON A.FieldA = B.FieldA
WHERE B.FieldA IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mkingrey]

Works great!!! thanks for the help

-M