Use a join to exclude items from query

[roseberryv]

Is there a way to create a query that excludes items if they appear in
a certain table? I know you can use an inner join to restrict results
to those items that appear in both tables, and an outer join to include
all the items in one table, and those from the other table that match.
But if you link table A, which contains information on all items, and
table B, which contains a subset of those items, can you indicate that
if an item appears in table B, exclude it from the query results?

 

[John Welch]

You can do an outer join and set this criterion for tableB.itemID : is
null
then you will get only records that have no corresponing record in table B
hth
-John

 

[Guest]

Use an outer join and test for NULL in TableB:

SELECT TableA.*
FROM TableA LEFT JOIN TableB
ON TableA.ID = TableB.ID
WHERE TableB.ID IS NULL;

Ken Sheridan
Stafford, England

 

[John Vinson]

But if you link table A, which contains information on all items, and
table B, which contains a subset of those items, can you indicate that
if an item appears in table B, exclude it from the query results?

Use a criterion of

IS NULL

on the joining field in TableB. Don't put criteria on any other TableB
fields though.

This is what I call a "frustrated outer join" query; it's how the
Unmatched Query Wizard works.

John W. Vinson[MVP]