MS Access Query AND and NOT IN operators not giving correct result

Question

Hello All,

I am facing very strange problem with MS access query, the query only considers first AND stmt and ignores the 2nd AND stmt resulting into incorrect result. below is my query

SELECT DISTINCT FIRST_NAME,Last_Name ,Study FROM [Temp1] WHERE [Study ] In (SELECT study_name from Temp1) AND ([First_Name ] NOT IN(SELECT First_Name from Temp)) AND ([Last_Name] NOT IN(SELECT Last_Name from Temp))

Expected Result: I want to find out all the study and all users who are present in Temp1 table but not present in Temp table

Answer 1

One problem with the NOT IN predicate is that it does not cater for NULLs. This can easily be seen if you expand the expression algebraically:

a NOT IN(x,y,NULL)

expands to:

(a <> x) AND (a <> Y) AND (a <> NULL)

which of course evaluates to NULL, neither TRUE nor FALSE, because nothing can ever be equal to NULL, even another NULL. The result will always be NULL. The way to avoid this is to use the NOT EXISTS predicate.

It is not clear from your question whether you wish to return all rows from Temp1 where the user is not represented in Temp, along with those rows where the Study value is not represented in Temp, i.e. the user's name and study are independent of each other. The query for this would be:

SELECT First_Name, Last_Name
FROM Temp1
WHERE NOT EXISTS
(SELECT *
FROM Temp
WHERE Temp.First_Name = Temp1.First_Name
AND Temp.Last_Name = Temp1.Last_Name)
OR NOT EXISTS
(SELECT *
FROM Temp
WHERE Temp.Study = Temp1.Study);

Alternatively you might want to return those rows where the user and Study values are not independent of each other, i.e. those rows where user names and Study values in combination are not represented in Temp. For this the query would be:

SELECT First_Name, Last_Name
FROM Temp1
WHERE NOT EXISTS
(SELECT *
FROM Temp
WHERE Temp.First_Name = Temp1.First_Name
AND Temp.Last_Name = Temp1.Last_Name
AND Temp.Study = Temp1.Study);

Answer 2

Does this give you what you needed?

SELECT * FROM Temp1
LEFT JOIN Temp
ON Temp1.First_Name=Temp.First_Name
AND Temp1.Last_Name=Temp.Last_Name
WHERE Temp.study_name Is Null