CODEWITHSUNDEEP
sqlstringselectwhere-clausesql-like
Eric
Eric

Reputation: 941

Query Not Like returning odd results

Simple Query that I am stumped on. I am trying to filter out jobs that end in 4120and so forth. When I run this query 

select Job.Job, Job.Status from Job 
    Where(Job.Job Not Like '%4120'
          And Job.Job Not Like '%4235'
          And Job.Job Not Like '%4236'
          And Job.Job Not Like '%5910'
          And Status = 'Active' OR Status = 'Complete') 
    Order By Job.Job

I Get these results

    Job         Status
    01-19-4120  Complete
    01-19-4235  Complete
    01-19-5910  Complete
    02-19-4120  Complete
    02-19-4235  Complete
    02-19-4236  Complete
    02-19-5910  Complete
    03-07-4120  Complete
    03-19-4120  Complete
    03-19-4235  Complete
    03-19-5910  Complete
    04-19-4120  Complete
    04-19-4160  Complete

Is there something wrong with my query?

Upvotes: 0

Views: 51

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You don't need parentheses if you use in:

Where Job.Job Not Like '%4120' and
      Job.Job Not Like '%4235' and
      Job.Job Not Like '%4236' and
      Job.Job Not Like '%5910' and
      Status in ('Active', 'Complete')

Or, if your databases support left() as a string function:

where left(Job.Job, 4) not in ('4120', '4235', '4236', '5910') and
      Status in ('Active', 'Complete')

Upvotes: 1

GMB
GMB

Reputation: 222672

You have a logical prescendence problem. I highly suspect that you should be surrounding the ORed conditions with parentheses, as follows:

Job.Job Not Like '%4120'
And Job.Job Not Like '%4235'
And Job.Job Not Like '%4236'
And Job.Job Not Like '%5910'
And (Status = 'Active' OR Status = 'Complete')

Explanation: since logical operator OR has lower prescendence than AND, your WHERE conditions, as they are, are actually equivalent to:

(
    Job.Job Not Like '%4120'
    And Job.Job Not Like '%4235'
    And Job.Job Not Like '%4236'
    And Job.Job Not Like '%5910'
    And Status = 'Active' 
) OR Status = 'Complete'

This allows any record whose Status is 'Complete', regardless of the value of Job, which is not what you want.

Also, please note that it should be possible to simplify these conditions by using a string function instead of Not Likes and an in condition instead of Or. Assuming that your RDBMS supports right():

right(Job.Job, 4) not in ('4120', '4235', '4236', '5910') 
and Status in ('Complete', 'Active')

Upvotes: 2

zip
zip

Reputation: 4061

if sql server Try this:

select Job.Job, Job.Status from Job 
    Where right(rtrim(Job.Job),4) Not in ('4120','4235','4236','5910')
          And Status = 'Active' OR Status = 'Complete') 
    Order By Job.Job

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29649

It's a brackets problem. By including the OR within the brackets, your saying "get me all the things that match these strings and have a status of active or have a status of complete - the OR will match records that don't match your other criteria.

select Job.Job, Job.Status from Job 
    Where(Job.Job Not Like '%4120'
          And Job.Job Not Like '%4235'
          And Job.Job Not Like '%4236'
          And Job.Job Not Like '%5910')
    And (Status = 'Active' OR Status = 'Complete') 
Order By Job.Job

Upvotes: 2

dvo
dvo

Reputation: 2153

You need parenthesis around your Status = 'Active' OR Status = 'Complete' because right now your query executes as: 

WHERE (Job.Job Not Like '%4120'
          And Job.Job Not Like '%4235'
          And Job.Job Not Like '%4236'
          And Job.Job Not Like '%5910'
          And Status = 'Active') OR (Status = 'Complete')

So you will get all records where Status = 'Complete' regardless of Job.Job. To solve this try adding parenthesis around your status check:

Where(Job.Job Not Like '%4120'
      And Job.Job Not Like '%4235'
      And Job.Job Not Like '%4236'
      And Job.Job Not Like '%5910'
      And (Status = 'Active' OR Status = 'Complete'))

Upvotes: 2

Related Questions