How to exclude one ID from selected columns

user_16253384 0
Viewed 54

So far I have the following query

SELECT r.InjuryState 
FROM dbo.Referral r 
WHERE r.InjuryState IN ('AL', 'CA', 'CO', 'FL', 'KS', 'LA', 'MI', 'MO', 'NC', 'NE', 'NJ', 'NV', 'PA', 'TN', 'TX')

What I need to do is exclude a certain ReferralServiceID it just happens to be 10 but only if the injury state = 'CA'

I know I can do that by doing this query

SELECT r.InjuryState, r.ReferralServiceID 
FROM dbo.Referral r 
WHERE r.InjuryState = 'CA' AND r.ReferralServiceID <> '10'

However I cannot figure out how (or if it is even possible) to combine the two queries into one

tsql sql
2 Answers

Like this:

SELECT r.InjuryState 
FROM dbo.Referral r 
WHERE r.InjuryState IN ('AL', 'CA', 'CO', 'FL', 'KS', 'LA', 'MI', 'MO', 'NC', 'NE', 'NJ', 'NV', 'PA', 'TN', 'TX')
  AND NOT (r.InjuryState = 'CA' AND r.ReferralServiceID = '10')
user_1091220

You update the logic, remove 'CA' from IN and combine with OR clause: NOTE: One can always replace IN with a series of OR's, if one has to:

Try this please, it has NOT been tested:

SELECT r.InjuryState FROM dbo.Referral r 
 WHERE 
 (
    (r.InjuryState IN ('AL',  'CO', 'FL', 'KS', 'LA', 'MI', 'MO', 'NC', 'NE', 'NJ', 'NV', 'PA', 'TN', 'TX'))
    OR
    ( (r.InjuryState = 'CA') AND (r.ReferralServiceID <> '10') )
)
user_425520
Related