Table subtraction

[mattia]

How can I display the values in a field that are NOT in a table but are
in another one?

E.g.

Table #1
ID
1
2
3
4
5
6

Table #2
ID
1
2
5
6

Result -->
ID
3
4

Thanks, Mattia

 

[Dale_Fye via AccessMonster.com]

You could use the query design wizard to create an Unmatched query, or do it
yourself. With the table structure you have given us, the SQL would look
like:

SELECT ID
FROM Table1
LEFT JOIN Table2
ON Table1.ID = Table2.ID
WHERE Table2.ID IS NULL

HTH
Dale

 

[vanderghast]

Have you tried the wizard about finding unmatched records? It produces an
outer join:

SELECT a.*
FROM a LEFT JOIN b ON a.f1=b.f1
WHERE b.f1 IS NULL


Sure, that may not what you expect, if there are duplicated values: Someone
may expect that {1, 1, 1, 2, 3} - { 1, 1, 2} produces { 1, 3} since
only two 1 are asked to be removed, leaving one in the result, but with the
query here up, you would get simply {3}, removing all records having a
possible match, ie, removing all 1, even if the subtractor has less of them
than the set we subtracted from.


If you prefer, you can also use a NOT IN, but that is slower:


SELECT *
FROM a
WHERE a.f1 NOT IN (SELECT f1
FROM b)




Vanderghast, Access MVP

 

[mattia]

Il Wed, 22 Jul 2009 17:31:19 +0000, mattia ha scritto:

How can I display the values in a field that are NOT in a table but are
in another one?

E.g.

Table #1
ID
1
2
3
4
5
6

Table #2
ID
1
2
5
6

Result -->
ID
3
4

Thanks, Mattia

Apparently today I discovered that the query wizard is quite useful!
Thanks.