How do I correctly use two Not Exists statements in a where clause using Access SQL VBA?

Question

I have 3 Tables: NotHeard,analyzed,analyzed2. In each of these tables I have two columns named UnitID and Address.

What I'm trying to do right now is to select all of the records for the columns UnitID and Address from NotHeard that don't appear in either analyzed or analyzed2. The SQL statement I created was as follows:

SELECT UnitID, Address  
INTO [NotHeardByEither] 
FROM [NotHeard] 
Where NOT EXISTS( Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID) 
or NOT EXISTS( Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID) 
Group BY UnitID, Address 

I thought this would work since I've used the single NOT EXISTS subquery line and it has worked just fine for me in the past. The above query however returns the same data that is in the NotHeard table whereas if I take out the or NOT EXISTS part it works correctly.

Any ideas as to what I'm doing wrong or how to do what I'm wanting to do?

Vetiver

Answer 1

Regarding you original query, try

NOT (
   EXISTS( 
    Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID) 
AND EXISTS( 
    Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID) 
    )

which would mean not in either. But that's equal to what you had originally (tested on sample data). Are you sure that you don't mean not in A neither in B? That would be

NOT (
   EXISTS( 
    Select analyzed.UnitID FROM analyzed WHERE [NotHeard].UnitID = analyzed.UnitID) 
OR EXISTS( 
    Select analyzed2.UnitID FROM analyzed2 WHERE [NotHeard].UnitID = analyzed2.UnitID) 
    )

Do realize that the EXISTS solution is using correlated subqueries which might perform worse then LEFT JOIN and NULLs, here's a sample.

SELECT NotHeard.UnitID, NotHeard.Address
FROM (NotHeard LEFT JOIN analyzed ON NotHeard.UnitID = analyzed.UnitID) 
     LEFT JOIN analyzed2 ON NotHeard.UnitID = analyzed2.UnitID
WHERE analyzed.UnitID Is Null OR analyzed2.UnitID Is Null
GROUP BY NotHeard.UnitID, NotHeard.Address;

Notice that I used OR in the condition as compared to Austin's solution, AND would give you neither in analyzed nor in analyzed2.

Theotheobald

Also, all subqueries, correlated or not, are poorly optimized by Jet/ACE when the NOT keyword is used. For some reasons, when NOT is involved, you can't depend on the engine using the indexes on both sides of the comparison. I don't know why this is the case, but I've seen it many times. Unfortunately Jet SHOWPLAN does not explain its subquery optimization (it was never implemented in SHOWPLAN). – July

Answer 2

It looks like you want left joins instead. I may be a little off on my Access syntax.

SELECT UnitID, Address  
INTO [NotHeardByEither] 
FROM [NotHeard] 
    LEFT JOIN [analyzed] ON [NotHeard].UnitID = [analyzed].UnitID
    LEFT JOIN [analyzed2] ON [NotHeard].UnitID = [analyzed2].UnitID
WHERE IsNull([analyzed].UnitID)
    AND IsNull([analyzed2].UnitID)
Group BY UnitID, Address 

Julissa

I can't seem to make this work, I keep on getting a Run-time error around the left joins – Vetiver

Use visual query builder in access :) – Theotheobald

lol, hadn't thought of that. I'm so used to typing them in myself I forget that it's there. – Vetiver

Wow, what a masochist. Why would anyone want to type any more SQL than they have to? – July

Because they are for VBA code. I just type them in there since most of them I've done similar things before it's just changing the names so I copy, paste and then make the changes. – Vetiver