SSS SSS - 1 year ago 47
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 Source

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)