Use a join to exclude items from query

  • Thread starter Thread starter roseberryv
  • Start date Start date
R

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?
 
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
 
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
 
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?
Click to expand...

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]
 
Back
Top