ashwin ashwin - 1 month ago 8
SQL Question

Subtracting Lists from Queries in Access

I have five fields in a Master Query named Suche. Lets consider the names of the fields for an easy demonstration as A,B,C,D,E. The Suche Query looks something like this

A B C D E
1 a b d
2 m n o
3 t z
4 r m d b
5 q f j


I have extracted two queries from the Suche Query. The First Query is called DUN and it extracts the following from Suche Query

A B C D E
1 a b d


and the 2nd Query is called HOL and it extracts the following from Suche Query.

A B C D E
3 t z
4 r m d b


Now I am looking to Build a Third Query where I want to find the remaining unextracted records from the Suche Query. I have tried to achieve it something like this

SELECT (Suche.A-DUN.A-HOL.A) AS FINAL FROM Suche,DUN,HOL;


But it doesn't seem to work. Can someone guide me on how to achieve this?

Answer

One method uses not in or not exists. Assuming that column A is unique, you can do:

select s.*
from (<Suche>) as s
where s.A not in (select d.a from (<DUN>) as d) and
      s.A not in (select h2.a from (<HOL>) as h2);
Comments