SSS SSS - 1 month ago 4
SQL Question

SQL Case NOT IN not working

Why does SQL Case 'not in' not work? 'in' works though it does not match for 'not in'.

Or perhaps there is a better way to do it?

I am trying to apply a result to a match that meets on criteria set so long as it does not meet the other criteria set.

Thank you



---------- Newsletters ----------

CASE WHEN ( C.ID in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel = 0 AND ReferenceTable <> 'Enquiry')
AND C.ID not in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 0 AND ReferenceTable <> 'Enquiry')
)
OR
( C.ID in (SELECT [ContactSpouseID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel = 0 AND ReferenceTable <> 'Enquiry')
AND C.ID not in (SELECT [ContactSpouseID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 0 AND ReferenceTable <> 'Enquiry')
)
OR
( ESC.ContactEnquiryID in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel = 0 AND ReferenceTable = 'Enquiry')
AND ESC.ContactEnquiryID not in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 0 AND ReferenceTable = 'Enquiry')
)
THEN 'K'

ELSE

CASE WHEN ( C.ID in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 0 AND YearLevel < 7 AND ReferenceTable <> 'Enquiry')
AND C.ID not in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel = 0 AND ReferenceTable <> 'Enquiry')
AND C.ID not in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 6 AND ReferenceTable <> 'Enquiry')
)
OR
( C.ID in (SELECT [ContactSpouseID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 0 AND YearLevel < 7 AND ReferenceTable <> 'Enquiry')
AND C.ID not in (SELECT [ContactSpouseID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel = 0 AND ReferenceTable <> 'Enquiry')
AND C.ID not in (SELECT [ContactSpouseID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 6 AND ReferenceTable <> 'Enquiry')
)
OR
( ESC.ContactEnquiryID in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 0 AND YearLevel < 7 AND ReferenceTable = 'Enquiry')
AND ESC.ContactEnquiryID not in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel = 0 AND ReferenceTable = 'Enquiry')
AND ESC.ContactEnquiryID not in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 6 AND ReferenceTable = 'Enquiry')
)

THEN '1-6'

ELSE

CASE WHEN ( C.ID in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 6 AND YearLevel < 13 AND ReferenceTable <> 'Enquiry')
AND C.ID not in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel < 7 AND ReferenceTable <> 'Enquiry')
)
OR
( C.ID in (SELECT [ContactSpouseID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 6 AND YearLevel < 13 AND ReferenceTable <> 'Enquiry')
AND C.ID not in (SELECT [ContactSpouseID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel < 7 AND ReferenceTable <> 'Enquiry')
)
OR
( ESC.ContactEnquiryID in (SELECT [ContactSpouseID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 6 AND YearLevel < 13 AND ReferenceTable = 'Enquiry')
AND ESC.ContactEnquiryID not in (SELECT [ContactSpouseID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel < 7 AND ReferenceTable = 'Enquiry')
)
THEN '7-6'

ELSE

CASE WHEN ( C.ID in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND ReferenceTable <> 'Enquiry')
)
OR
( C.ID in (SELECT [ContactSpouseID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND ReferenceTable <> 'Enquiry')
)
OR
( ESC.ContactEnquiryID in (SELECT [ContactID] FROM [Customers].[dbo].[uvLSProspectiveContacts] WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND ReferenceTable = 'Enquiry')
)

THEN 'General'

ELSE '' END END

END
END AS ProspectiveNews,




Answer

Try NOT EXISTS:

AND NOT EXISTS (SELECT * FROM [Customers].[dbo].[uvLSProspectiveContacts]
WHERE [EnrolDate] > '2016-12-31 00:00:00.000' AND YearLevel > 0 AND
ReferenceTable <> 'Enquiry' AND [ContactID] = C.ID)