SQL NOT IN CLAUSE

[rdemyan via AccessMonster.com]

I cannot figure out why the following SQL statement is not producing the
correct results:

SELECT Site, Count(BUILDING_ID) AS [Bldg Count]
FROM [TABLEA]
WHERE Site NOT IN (SELECT Site FROM [TableB] WHERE ALLOWED_TO_VIEW = True And
User_ID = 'john_doe')
GROUP BY Site
ORDER BY Site;

If you select Site from each of the tables and group by Site, you find that
TableA has 76 sites and TableB has 75 sites. So why wouldn't the one site
that is in TableA but not in TableB be returned. Both tables have a null site
(not my doing, but I have to live with it).

 

[pietlinden]

probably because one Null does not equal another Null. Think of Null
as "value undetermined" or something like that. Not the same as zero.
So that record gets removed from the results.

 

[OfficeDev18 via AccessMonster.com]

I would do this a different way; see what this does for you:

SELECT [TABLEA].Site, Count([TABLEA].BUILDING_ID) AS [Bldg Count]
FROM [TABLEA], LEFT JOIN [TableB] ON [TableA].Site = [TableB].Site
WHERE (ALLOWED_TO_VIEW = True) And (User_ID = 'john_doe') And ([TableB].Site
Is Null)
GROUP BY Site
ORDER BY Site;

Hope this helps,

Sam