CODEWITHSUNDEEP
phpmysql
Steven
Steven

Reputation: 13975

MySQL between and or?

I have a query for selecting orders and I want to select between two dates. But I also want to select all orders that are of one of 3 or 4 statuses. I have the following

SELECT *
WHERE  created BETWEEN '1296360000' AND '1322884800'
       AND order_status = 'pending'
        OR order_status = 'processing'
        OR order_status = 'payment_received'
        OR order_status = 'completed'

But the problem is it seems to select everything with order status of one of those 4. And completely ignores the BETWEEN if I change the order of the BETWEEN to be after the order status OR's it still includes orders with a created timestamp after the '1322884800'

Where should I put the between or how can I do this to select orders between X and Y with a order status of one of 4 (or 3, or 2, or 1) value(s)?

Upvotes: 0

Views: 152

Answers (7)

Thomas BP
Thomas BP

Reputation: 1227

or

SELECT * 
WHERE created >= '1296360000' 
AND created <= '1322884800' 
AND order_status in ("pending", "processing", "payment_received", "completed")

Upvotes: 0

Teena Thomas
Teena Thomas

Reputation: 5239

you need to explicitly define the precedence by using brackets:

 SELECT *
 WHERE  created BETWEEN '1296360000' AND '1322884800'
   AND ( order_status = 'pending'
    OR order_status = 'processing'
    OR order_status = 'payment_received'
    OR order_status = 'completed')

Upvotes: 0

virenpatel111
virenpatel111

Reputation: 201

SELECT *
WHERE  created BETWEEN '1296360000' AND '1322884800'
       AND ( order_status = 'pending'
        OR order_status = 'processing'
        OR order_status = 'payment_received'
        OR order_status = 'completed' )

Upvotes: 0

Glavić
Glavić

Reputation: 43552

SELECT *
WHERE
    created BETWEEN '1296360000' AND '1322884800' AND
    order_status IN ('pending', 'processing', 'payment_received', 'completed')

Upvotes: 2

Jonnix
Jonnix

Reputation: 4187

You need to encapsulate the ORs. Or use something like this:

SELECT * WHERE created BETWEEN '1296360000' AND '1322884800' AND order_status IN ('pending', 'processing','payment_received', 'completed')

Note, not checked for correctness.

Upvotes: 1

Bill the Lizard
Bill the Lizard

Reputation: 405765

Put the order_status clause in parentheses (or better yet, use an IN list).

SELECT *
WHERE  created BETWEEN '1296360000' AND '1322884800'
       AND (order_status = 'pending'
        OR order_status = 'processing'
        OR order_status = 'payment_received'
        OR order_status = 'completed')

The way you have it now, the precedence is being interpreted as:

SELECT *
WHERE  (created BETWEEN '1296360000' AND '1322884800'
       AND order_status = 'pending')
        OR order_status = 'processing'
        OR order_status = 'payment_received'
        OR order_status = 'completed'

This would evaluate to true if any of the last three order status are found, regardless of the created date.

Upvotes: 2

andrewsi
andrewsi

Reputation: 10732

It's a precedence issue:

SELECT *
WHERE  created BETWEEN '1296360000' AND '1322884800'
   AND (order_status = 'pending'
    OR order_status = 'processing'
    OR order_status = 'payment_received'
    OR order_status = 'completed')

The OR statements aren't being evaluated in the order you expect - put brackets around the OR part to make sure that they're evaluated properly. You can also do:

SELECT *
WHERE  created BETWEEN '1296360000' AND '1322884800'
   AND order_status IN ('pending','processing','payment_received','completed')

Which is a little neater.

Upvotes: 1

Related Questions