CODEWITHSUNDEEP
mysqlsql
nicksource
nicksource

Reputation: 99

MySQL multiple WHERE AND/OR condition logic

I want to have an SQL WHERE statement which is (this or this or this) AND (this or this or this)

So any of the first parentheses AND any of the second parentheses.

AND ((li.MSAttributeID = 82 AND li.MSAttributeValID = 5) OR (li.MSAttributeID = 82 AND li.MSAttributeValID = 6))
AND ((li.MSAttributeID = 85 AND li.MSAttributeValID = 223) OR (li.MSAttributeID = 85 AND li.MSAttributeValID = 229) OR (li.MSAttributeID = 85 AND li.MSAttributeValID = 247))

Why won't this return any results?

Doing just the first part, works fine:

AND ((li.MSAttributeID = 82 AND li.MSAttributeValID = 5) OR (li.MSAttributeID = 82 AND li.MSAttributeValID = 6))

and so does if I just do the second part:

 AND ((li.MSAttributeID = 85 AND li.MSAttributeValID = 223) OR (li.MSAttributeID = 85 AND li.MSAttributeValID = 229) OR (li.MSAttributeID = 85 AND li.MSAttributeValID = 247))

But not both combined. Which should return results if the way I'm thinking is right!

Upvotes: 0

Views: 107

Answers (3)

forpas
forpas

Reputation: 164099

Your conditions simplified for readability can be written like this:

AND ((x = 82 AND y = 5) OR (x = 82 AND y = 6))
AND ((x = 85 AND y = 223) OR (x = 85 AND y = 229) OR (x = 85 AND y = 247))

or even simpler:

AND (x = 82 AND y IN (5, 6))
AND (x = 85 AND y IN (223, 229, 247))

Now since there are only ANDs the parentheses can be removed and the condition is:

AND x = 82 AND y IN (5, 6) AND x = 85 AND y IN (223, 229, 247)

As you can see you have:

x = 82 AND x = 85

which is always false and also:

y IN (5, 6) AND y IN (223, 229, 247)

which is always false.

So the result of the Boolean expression is false and you get no results.

Maybe you want something like this:

AND (
  (li.MSAttributeID = 82 AND li.MSAttributeValID IN (5, 6))
  OR 
  (li.MSAttributeID = 85 AND li.MSAttributeValID IN (223, 229, 247))
)

Upvotes: 1

J. Schmale
J. Schmale

Reputation: 486

In your first line you have 2 combinations of MSAttributeId and MSAttributeValID. Then in your second line you have another 3 combinations. I think what you want is to return results that match any of those 5 combinations. So what you really want is:

AND 
(
   (li.MSAttributeID = 82 AND li.MSAttributeValID = 5) 
OR (li.MSAttributeID = 82 AND li.MSAttributeValID = 6)
OR (li.MSAttributeID = 85 AND li.MSAttributeValID = 223) 
OR (li.MSAttributeID = 85 AND li.MSAttributeValID = 229) 
OR (li.MSAttributeID = 85 AND li.MSAttributeValID = 247)
)

I will add that the reason your two lines joined with the AND returned no results is because by doing so you were saying that MSAttribteID had to equal both 82 and 85 at the same time - which is not possible.

Upvotes: 0

Jim Macaulay
Jim Macaulay

Reputation: 5141

You have to use OR instead of AND in the second condition

AND ((li.MSAttributeID = 82 AND li.MSAttributeValID = 5) OR (li.MSAttributeID = 82 AND li.MSAttributeValID = 6))
OR ((li.MSAttributeID = 85 AND li.MSAttributeValID = 223) OR (li.MSAttributeID = 85 AND li.MSAttributeValID = 229) OR (li.MSAttributeID = 85 AND li.MSAttributeValID = 247))

AND makes both the conditions valid and returns no results

Below is the right way of providing the condition,

 AND ((li.MSAttributeID in (82, 85) AND li.MSAttributeValID in (5, 6, 223, 229, 247 )) OR (li.MSAttributeID = 82 AND li.MSAttributeValID = 6))

Upvotes: 1

Related Questions