CODEWITHSUNDEEP
mysql
JasonDavis
JasonDavis

Reputation: 48933

MySQL query with multiple OR conditions and a single AND condition

I have a MySQQL database table in which I dynamicly build the SQL statement with PHP based on some user input.

The SQL end up looking something like this....

SELECT * 
FROM  `nam_order_items` 
WHERE item_status =  'Glass Bending - Andy'
OR item_status =  'Glass Bending - Bren'
OR item_status =  'Glass Bending - Gary'
OR item_status =  'Glass Bending - James'
OR item_status =  'Glass Bending - Oscar'
AND (
`backing_cut` =0
)

Basically DB column item_status can be any of them and column backing_cut can be 0 or 1.

With this SQL above, it returns all the records matching the item_status values regardless of there backing_cut value.

It should allow me to filter out records from the result based on the backing_cut being a value of 0 or 1.

What am I doing wrong?

In addition to above SQL I also tried....

SELECT * 
FROM  `nam_order_items` 
WHERE item_status =  'Glass Bending - Andy'
OR item_status =  'Glass Bending - Bren'
OR item_status =  'Glass Bending - Gary'
OR item_status =  'Glass Bending - James'
OR item_status =  'Glass Bending - Oscar'
AND `backing_cut` =0

AND

SELECT * 
FROM  `nam_order_items` 
WHERE item_status =  'Glass Bending - Andy'
OR item_status =  'Glass Bending - Bren'
OR item_status =  'Glass Bending - Gary'
OR item_status =  'Glass Bending - James'
OR item_status =  'Glass Bending - Oscar'
(
    AND `backing_cut` =0
)

Upvotes: 1

Views: 9575

Answers (3)

mani
mani

Reputation: 3096

Use parentheses

SELECT * 
FROM  `nam_order_items` 
WHERE (item_status =  'Glass Bending - Andy'
OR item_status =  'Glass Bending - Bren'
OR item_status =  'Glass Bending - Gary'
OR item_status =  'Glass Bending - James'
OR item_status =  'Glass Bending - Oscar')
AND (`backing_cut` = 0 OR `backing_cut` = 1 OR `backing_bent` = 0 OR `backing_bent` = 1)

you could also use value in array as

SELECT * 
FROM  `nam_order_items` 
WHERE `item_status` IN  ('Glass Bending - Andy','Glass Bending - Bren','Glass Bending - Gary','Glass Bending - James','Glass Bending - Oscar')
AND (`backing_cut` = 0 OR `backing_cut` = 1 OR `backing_bent` = 0 OR `backing_bent` = 1)

you could then build your array using php and simply do something like:

$options = array('Glass Bending - Andy','Glass Bending - Bren','Glass Bending - Gary','Glass Bending - James','Glass Bending - Oscar');

$sql = "SELECT * FROM `nam_order_items` WHERE `item_status` IN ({implode(',', $options}) AND (`backing_cut` = 0 OR `backing_cut` = 1 OR `backing_bent` = 0 OR `backing_bent` = 1)";

Edited to add option for backing cut = 1 or 0

Upvotes: 7

Ruslan Stelmachenko
Ruslan Stelmachenko

Reputation: 5420

To make it easier to understand why so think about operators in terms you familiar with.

The OR and AND operators precedence is like + and *.

So if you do 1 + 1 + 1 * 2 or 1 + 1 + 1 * (2) or 1 + 1 + 1 (* 2) the result is always 4. The * is always first operator to execute. If you want the result to be 6 then you need (1 + 1 + 1) * 2.

Upvotes: 1

Ctx
Ctx

Reputation: 18410

A conjunction has precedence over a disjunction, so 

SELECT * 
FROM  `nam_order_items` 
WHERE item_status =  'Glass Bending - Andy'
OR item_status =  'Glass Bending - Bren'
OR item_status =  'Glass Bending - Gary'
OR item_status =  'Glass Bending - James'
OR item_status =  'Glass Bending - Oscar'
AND `backing_cut` =0

AND

SELECT * 
FROM  `nam_order_items` 
WHERE item_status =  'Glass Bending - Andy'
OR item_status =  'Glass Bending - Bren'
OR item_status =  'Glass Bending - Gary'
OR item_status =  'Glass Bending - James'
OR item_status =  'Glass Bending - Oscar'
(
    AND `backing_cut` =0
)

are semantically identical. You have to use brackets to group the disjunctions to take precedence over the conjunction:

SELECT * 
FROM  `nam_order_items` 
WHERE item_status =  ('Glass Bending - Andy'
OR item_status =  'Glass Bending - Bren'
OR item_status =  'Glass Bending - Gary'
OR item_status =  'Glass Bending - James'
OR item_status =  'Glass Bending - Oscar' )
AND `backing_cut` = 0

Upvotes: 1

Related Questions