asked on

How to find records that don't exist in second table (Not Exists or Join)??

Dear all,

I have two tables (see below) I would like to return the records in ModelDataTable where the Period and Station cannot be found in the Records table (field names are Day and Location).

ModelDataTable
Period - Date
District - Text
Name - Text
Station - Number

RecordsTable
Day - Date
Location - Number

I've tried NOT EXISTS and JOIN without success. No results are returned even though there are many records that don't exist.

SELECT ModelDataTable.Period, ModelDataTable.District, ModelDataTable.Name, ModelDataTable.Station
FROM ModelDataTable 
WHERE NOT EXISTS (SELECT RecordsTable.Day FROM RecordsTable WHERE Not ModelDataTable.Period = RecordsTable.Day AND ModelDataTable.Station = RecordsTable.Location );

Open in new window

SELECT ModelDataTable.Period, ModelDataTable.District, ModelDataTable.Name, ModelDataTable.Station
FROM ModelDataTable INNER RecordsTable ON (ModelDataTable.Station = RecordsTable.Location) AND (ModelDataTable.Period = RecordsTable.Day)
WHERE (Not (ModelDataTable.Period)=[RecordsTable].[Day]) AND (ModelDataTable.Station = RecordsTable.Location);

Open in new window

Thanks

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

By the way, outer joins are faster. When you use EXISTS, your using a sub query, which executes a SELECT for each and every row.

Also when your doing this in the query designer, you can select the join type by joining two tables, then double clicking on the join line. You'll get a dialog that lets you select an equi-join (default), or a left or right outer join (they are the same, just depends on which side of the join each table is.

Jim.