Problem w/ WHERE clause 1

[jiggyg]

Hello List!

I have a question on why my result sets aren't returning as I expect...

If I run:

SELECT *
FROM [dbo].[ClientIssues](1,1)
WHERE usr_full_name = 'myName'
OR usr_full_name = 'yourName'

I'm getting the results I expect: only results w/ 'myName' or 'yourName' in the usr_full_name column.

If I run:

SELECT *
FROM [dbo].[ClientIssues](1,1)
WHERE usr_full_name = 'myName'
AND AssignedTo= 'yourName'

Again, getting the results I expect: only results w/ 'myName' in the usr_full_name col and 'yourName' in AssignedTo column.

However...when I try to combine them:

SELECT *
FROM [dbo].[ClientIssues](1,1)
WHERE usr_full_name = 'myName'
OR usr_full_name = 'yourName'
AND AssignedTo= 'yourName'

My results are NOT what I expected...I'd expect to see ONLY the records w/ either 'myName' or 'yourName' in the usr_full_name col AND ONLY 'yourName' in the AssignedTo column. But, that's not what I'm getting...I'm getting results w/ either 'myName' or 'yourName' in the usr_full_name col and in the AssignedTo column, there are more than just 'yourName'. Not the same result set as the first query (which returned 112 rows) -- this one returns 21 less rows.

Any ideas/suggestions?

Thanks much!
-jiggyg

 

[gmmastros]

Because the AND is getting processed before the OR. Including parenthesis will solve your problem.

SELECT *
FROM [dbo].[ClientIssues](1,1)
WHERE [!]([/!]usr_full_name = 'myName'
OR usr_full_name = 'yourName'[!])[/!]
AND AssignedTo= 'yourName'

As a habit, I ALWAYS use parenthesis in where clauses when I use OR. It's a good habit to get in to.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jiggyg]

Thanks again, George! Helped me with two issues today!

And, you're right about getting into the habit of always using them -- I'll be sure to start doing that!

A STAR for you for the quick and distinct reply!

 

[SQLSister]

I agree about that habit. Even when you know that the results will return the way you intended without the parentheses, your intent is not obvious to the person has to maintain that proc six months after you've left the company.

"NOTHING is more important in a database than integrity." ESquared