Am I doing something wrong here, it's a simple query and it's not work 1

[sd0t1]

I have a simple query that consistantly returns the wrong results which makes me think I'm missing something here.
The query is:

SELECT * FROM `helpdesk` WHERE user = 'smith' OR mgr = 'smith' AND complete = '2'

This should return all rows with smith as the user or the manager and only if the 'complete' value is 2.

However I get complete with 1 as the value in my result set.

Can anyone tell me what I'm doing wrong?

 

[r937]

what you are doing wrong is mixing ANDs and ORs without the appropriate parentheses for your requirements

ANDs take precedence over ORs

what you wrote --

WHERE user = 'smith' OR mgr = 'smith' AND complete = '2'

this is equivalent to --

WHERE user = 'smith' OR ( mgr = 'smith' AND complete = '2' )

what you probably wanted --

WHERE ( user = 'smith' OR mgr = 'smith' ) AND complete = '2'

see the difference? ;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[sd0t1]

I do see the difference. I tried it and it works. I can't believe I've never ran into this before over the past 4 years that I've been doing this.

I really appreciate your fast response, have a great day.

 

[r937]

you could alternatively write this --

WHERE 'smith' IN ( user , mgr ) AND complete = '2'



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon