I have an app where users can select various options off a page and the behind code builds a SQL query to return the relevant records.
In this case a user has selected:
- Division != '1'
There is also an automatic restriction, in that the Division of returned records has to be in the UserDivisions, which is a variable that looks like
Division IS NULL OR Division IN ('1','1.1','1.1.1')
So returned records have to be in one of the listed divisions, or have NULL as a value.
My code is currently processing these restrictions separately, so the final SQL looks like
Select top 1000 Division, WoNum, WoType
From WorkOrder
Where (Division IS NULL OR Division IN ('1','1.1','1.1.1'))
AND (Division <> '1')
This query returns all the records with Division = '1.1' and '1.1.1', and it doesn't return any in Division '1', like it should, but it also doesn't return any records with Division = Null.
How can I build this query to return NULLs as well, and why doesn't the above query return NULLs?