Join Query - How do I subtract one set from another?

[BlueWolvering]

I am trying to find every entry on list A that is NOT on list B. So I am
trying to subtract list B out of list A. So far, all I have been able to do
is get rid of the intersection, such that I have every entry not in both.

The code for that is

SELECT A.ID FROM A LEFT JOIN B ON A.ID = B.ID WHERE B.ID IS NULL UNION
SELECT B.ID FROM B LEFT JOIN A ON B.ID = A.ID WHERE A.ID IS NULL;

How do I get rid of the entries in List B?

By the way, a right join in the above code produces an empty set.

I am using Microsoft Access 2003 and do NOT have options to switch versions.
Resources at my disposal are the internet, and The Microsoft Access 2000
Bible by Cary N. Prague and Michael R. Irvin. (Figures right?)

Don't feel obligated to answer in the form of a page reference from that
book, I include it because maybe it helps someone help me. If it's not
useful to your reply, disregard.

Thank you.

 

[Michel Walsh]

The query wizard proposes a way to do it, if you have a primary key. Else,
the generic solution would be:

SELECT a.*
FROM a LEFT JOIN b
ON a.field1=b.field1 AND a.field2=b.field2 AND ... AND a.fieldN =
b.fieldN
WHERE b.field1 IS NULL



Hoping it may help,
Vanderghast, Access MVP

 

[BlueWolvering]

What would the WHERE conditions look like if the comparison was based on N
id's being the same?

Say trying to get all the records in A that are not in B but the two have
unique records based on A.id and A.Provider being a unique combination>?

 

[Michel Walsh]

It would be the same.

If there is no match (as defined by the ON clause), every fields that should
come from the unpreserved table, here, b, hold a null value. It is thus
enough to test a single one, in the ON clause.

Sure, if you are worried about "what if the original field so tested has
already a NULL in it?" Well, since NULL is never equal to anything, the ON
clause would not found a match on even such a field, so, that is not a
problem.


It is the ON clause that really matters, not much the WHERE clause, here.



Vanderghast, Access MVP

 

[BlueWolvering]

I have tested this and it does not work directly.

Table A has records uniquely Identified by fields A.1 and A.2 say, first
and last names

Table B has records uniquely identified as a primary key, B.1 B.2 (say again
first and last names.)

My problem is that if I search for people in list A not in list B, but the
first name in A matches the first name in B, the last name shows as NULL.
A

Tom Walsh
Jim Smith
Bobby Knight

B

Tom Harmon
Duke Cansor
Leo Fairfield


The list of those people in A not in B would look like this.
Jim Smith
Bobby Knight
(Tom) Walsh

where the cell for (Tom) is blanked.

Thoughts on that?

 

[Michel Walsh]

Something is wrong.


TableA
FirstName LastName
Tom Walsh
Jim Smith
Bobby Knight



TableB
FirstName LastName
Tom Harmon
Duke Cansor
Leo Fairfield



Then, the query:


SELECT TableA.FirstName, TableA.LastName
FROM TableA LEFT JOIN TableB ON (TableA.LastName = TableB.LastName) AND
(TableA.FirstName = TableB.FirstName)
WHERE (((TableB.FirstName) Is Null));

returns


Query4
FirstName LastName
Tom Walsh
Jim Smith
Bobby Knight



exactly what we expect.

Furthermore, there is no apparent reason about why only Tom Walsh would
become (NULL) Walsh, and not the other two records of the result.


The different result you got is probably because there is something else
involved (and that something is elusive). Maybe your database is corrupted.
Can you try on a new database?


Hoping it may help,
Vanderghast, Access MVP