show values not listed in a table

  • Thread starter Thread starter perryclisbee via AccessMonster.com
  • Start date Start date
P

perryclisbee via AccessMonster.com

I have a table [DRAP1] with a list of values( in field name: [ID]) that were
filtered from a base table ([DRAP_Base].[ID]) based on specific criteria.
What I want to do now is show all values in the base table (the remainder of
the ID values) that were NOT listed in the initial table [DRAP1].

How would I go about doing that?

Thanks for the help.

Perry
 
SELECT DRAP_Base.ID
FROM DRAP_Base
WHERE DRAP_Base.ID NOT IN (
SELECT DRAP1.ID
FROM DRAP1
WHERE DRAP_Base.ID = DRAP1.ID ) ;
 
Either by using an outer join and testing for NULL:

SELECT *
FROM DRAP_Base LEFT JOIN DRAP1
ON DRAP_Base.ID = DRAP1.ID
WHERE DRAP1.ID IS NULL;

or by means of a subquery:

SELECT *
FROM DRAP_Base
WHERE NOT EXISTS
(SELECT *
FROM DRAP1
WHERE DRAP1.ID = DRAP_Base.ID);

Ken Sheridan
Stafford, England
 
Back
Top