How to select records that do not exist in two (or more) tables

Question

I have 3 tables of accounts that all contain the same fields. Table1 contains all accounts while Table2 and Table3 contain subsets of the accounts. I'm trying to select records in Table1 that do no exist in Table2 or Table3.

Let's say the table layout is like this and is the same for all 3 tables: |AcctNum|Name|State|

I know how to do this if it was just Table1 and Table2, using a left join and Is Null, but the 3rd table is throwing me. Is this possible to do in one query? Can you combine left joins? I should point out I'm using Access 2010.

Answer 1

Yes you can combine left joins and with the odd syntax Access uses the query should look like this:

SELECT T1.AcctNum
FROM (Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.AcctNum = T2.AcctNum) 
LEFT JOIN Table3 AS T3 ON T1.AcctNum = T3.AcctNum
WHERE (((T2.AcctNum) Is Null) AND ((T3.AcctNum) Is Null));

Answer 2

You can use Access to create a view called TableCombined that is a union of both Table2 and Table3.

At that point, you can use your left join and Is Null query and join TableCombined to Table1.

Hope this helps!

Answer 3

You can also do a NOT EXISTS statement which makes sense logically for what you are trying to achieve.

For example:

SELECT ACCTNUM 
FROM TABLE1 
WHERE NOT EXISTS (SELECT TABLE2.ACCTNUM FROM TABLE2 INNER JOIN TABLE3 WHERE TABLE2.ACCTNUM IS NULL AND TABLE3.ACCTNUM IS NULL)