mickey
mickey

Reputation: 5

Date select.. wont work

I have a question. When executing the following SQL query:

SELECT order_id, orderdatum, plaats
FROM order_row 
INNER JOIN order_row_oneoff ON order_row.order_row_id = order_row_oneoff.order_row_oneoff_id
INNER JOIN orders ON order_row.order_id = orders.ordernr
WHERE orderdatum >= '2015-09-01'
AND orderdatum < '2015-09-05'
AND order_row.product_id = '4118' OR order_row.product_id = '4128' OR order_row.product_id = '4176' AND orders.orderstatus > 98 

It just selects everything and ignores the date. But when you remove the second AND clause, it works. Why?

Upvotes: 0

Views: 52

Answers (2)

LEADER
LEADER

Reputation: 113

Actually you have a problem in the last and as you should use an IN operator instead of AND.

 product_id IN ('4118', '4128' , '4176')

Upvotes: 0

juergen d
juergen d

Reputation: 204924

and has a higher operator precedence which means that and binds stronger than or. Use parentheses to put the conditions together

where orderdatum >= '2015-09-01'
and orderdatum < '2015-09-05'
and 
(
   order_row.product_id = '4118' 
   or  order_row.product_id = '4128' 
   or order_row.product_id = '4176'
)
and orders.orderstatus > 98 

or in your case use IN

where orderdatum >= '2015-09-01'
and orderdatum < '2015-09-05'
and order_row.product_id in ('4118', '4128' , '4176')
and orders.orderstatus > 98 

Upvotes: 1

Related Questions