All geek questions in one place

How to properly use two Not Exists statements in a where clause using Access SQL VBA?

I have 3 tables: NotHeard, parsed, parsed2. 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 the entries for the UnitID and Address from NotHeard columns that are not displayed in either the analyzed or the 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 used a single line of the NOT EXISTS subquery, and in the past this worked just fine. However, the above query returns the same data as in the NotHeard table, whereas if I remove the NOT NOT EXISTS part, it works correctly.

Any ideas on what I'm doing wrong or how to do what I want to do?

+4
source share
2 answers

Regarding the original request, 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 both. But this corresponds to what you initially (tested on sample data). Are you sure you do not mean not in neither B? It will 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) )

Understand that the EXISTS solution uses correlated subqueries, which can be worse than LEFT JOIN and NULL, 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;

Note that I used OR in a state compared to the Austin solution, and would not have given you either in the analysis or in the analysis2.

+5
source

It looks like you want to use left joins. Perhaps I am a bit disabled in 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
0
source

Source: https://habr.com/ru/post/1305805/

More articles:


All Articles