CODEWITHSUNDEEP
ms-accessselectsubquerywhere-clause
spassen
spassen

Reputation: 1580

Select where not in subquery Access 2010

I have a query that looks like the following:

SELECT *
FROM servTable, serialTable
WHERE servTable.[Service Tag]=serialTable.[Serial Number];

This query, assuming I haven't made any mistakes, should pull all of the information from both tables where the serial and service columns match.

I also need to pull all information from each table where there isn't a match. I've tried doing that with the following queries, but they don't seem to work.

SELECT *
FROM serialTable WHERE serialTable.[Serial Number] <> ( SELECT *
FROM servTable, serialTable
WHERE servTable.[Service Tag]=serialTable.[Serial Number] );

and

SELECT *
FROM servTable WHERE servTable.[Service Tag] <> ( SELECT *
FROM servTable, serialTable
WHERE servTable.[Service Tag]=serialTable.[Serial Number] );

Thanks.

Upvotes: 1

Views: 3837

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

1 Match

SELECT *
FROM servTable
INNER JOIN serialTable
ON servTable.[Service Tag]=serialTable.[Serial Number];

2 No Match, one side only

SELECT *
FROM servTable
LEFT JOIN serialTable
ON servTable.[Service Tag]=serialTable.[Serial Number]
WHERE serialTable.[Serial Number] Is Null

There are Find Matched and Find Unmatched query wizards in MS Access.

Upvotes: 3

Related Questions