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
AndyC1000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
This is how to do with the NOT EXISTS:
SELECT ModelDataTable.Period, ModelDataTable.District, ModelDataTable.Name, ModelDataTable.Station
FROM ModelDataTable 
WHERE NOT EXISTS (SELECT RecordsTable.Day FROM RecordsTable WHERE ModelDataTable.Period = RecordsTable.Day AND ModelDataTable.Station = RecordsTable.Location );

Open in new window

And this is how to do with LEFT JOIN:
SELECT ModelDataTable.Period, ModelDataTable.District, ModelDataTable.Name, ModelDataTable.Station
FROM ModelDataTable LEFT JOIN RecordsTable ON (ModelDataTable.Station = RecordsTable.Location) AND (ModelDataTable.Period = RecordsTable.Day)
WHERE RecordsTable.Location IS NULL;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.