CODEWITHSUNDEEP
sqlms-access
seppy
seppy

Reputation:

How do I get all the rows in one table that are not in another in MS Access?

I have tried a bunch of different things but always get syntax errors.

I have two tables - tableA and tableB. They both have a con_number field as a unique ID. I want to select all of the rows in tableB that do not exist in tableA.

Can anyone please give me this query as it would be in MS Access?

I know that using NOT IN is quite inefficient in this case so if there is a better way then that would be great.

Thanks.

Upvotes: 10

Views: 26142

Answers (4)

Fionnuala
Fionnuala

Reputation: 91376

There is a Find Unmatched wizard that will set this up. The SQL is:

SELECT TableB.con_number
FROM TableB LEFT JOIN TableA 
ON TableB.con_number = TableA.con_number
WHERE TableA.con_number Is Null

Upvotes: 3

Philippe Grondier
Philippe Grondier

Reputation: 11148

I remember something like this one:

SELECT * FROM TableA.* LEFT JOIN TableB _
    ON TableA.con_number = TableB.con_number WHERE 'criteria'

But I don't remember which 'criteria' to use

... TableA.con_number <> TableB.con_Number
... TableB.con_number IS NULL
... TableA.con_number NOT like TableB.con_Number

Upvotes: 0

soulmerge
soulmerge

Reputation: 75764

NOT IN version (slow but sure):

SELECT con_number
FROM TableB
WHERE con_number NOT IN (SELECT con_number FROM tableA);

experimental version (don't know if this is any faster, just try it out):

SELECT B.con_number, MAX(A.con_number) AS check
FROM tableB B LEFT JOIN tableA A ON B.con_number = A.con_number
GROUP BY B.con_number
HAVING check IS NULL;

Note: Both should be fairly standard SQL, I don't know any ms-access specific features

Upvotes: 2

Dave Barker
Dave Barker

Reputation: 6447

SELECT TableB.con_number
FROM TableB
WHERE NOT EXISTS (SELECT 1 
                  FROM TableA 
                  WHERE TableA.con_number = TableB.con_number);

Upvotes: 19

Related Questions