SQL include and exclude-CodePudding

I am trying to return customers that have had any of the following words appear on their account: 'Communication','Response','Registration', 'Investigation' And usually when any of the words above are on the account the word 'GS' should be added by the advisor onto the database. I'm trying to build a script to find the accounts where 'GS' has not been added as per below script. The script runs without error but still returns accounts with 'GS' and any of the words 'Communication','Response','Registration', 'Investigation'

Select* 
FROM ENQUIRY.enquiried a
Left Outer Join ENQUIRE.REQUEST_TYPE b 
ON a.request_type = b.ID
Where b.description NOT LIKE 'GS'
AND b.description in ('Communication','Response','Registration', 
'Investigation')

CodePudding user response：

Try this:

SELECT * FROM ENQUIRY.enquiried a
LEFT OUTER JOIN ENQUIRE.REQUEST_TYPE b 
    ON a.request_type = b.ID
WHERE b.description NOT LIKE 'GS%'
    AND b.description IN ('Communication','Response','Registration', 
'Investigation')

CodePudding user response：

Can you share some sample data and schema? I believe the not like condition shouldn't even be necessary as we are explicitly mentioning values with the 'IN' operator.

SELECT *
FROM   enquiry.enquiried a
       LEFT OUTER JOIN enquire.request_type b
                    ON a.request_type = b.id
WHERE  b.description IN ( 'Communication', 'Response', 'Registration',
                          'Investigation');