CODEWITHSUNDEEP
sqlms-access
rmobis
rmobis

Reputation: 26992

MS Access - WHERE IN works, but WHERE NOT IN fails

I have the following query (simplified) on MS Access:

SELECT * FROM table1 WHERE table1.ID NOT IN (SELECT DISTINCT table1id FROM table2);

My problem is it doesn't work, but these two ones work:

SELECT * FROM table1 WHERE table1.ID IN (SELECT DISTINCT table1id FROM table2);
SELECT * FROM table1 WHERE table1.ID NOT IN (2, 3);

The first one simply returns me an empty set, while I know I have records on table1 with ids ranging from 1 to 9, and only 2 and 3 are use on table 2.

Any help?

Upvotes: 3

Views: 33161

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Generally, the problem with IN and NOT in has to do with NULLs in the subselect. Try this and see if it works:

SELECT *
FROM table1
WHERE table1.ID NOT IN (SELECT DISTINCT table1id FROM table2 where tableid is not null);

Upvotes: 11

Related Questions