Problem with MySQL query

Question

Hi, i have a table that am trying to filter a particular ID (rid) that falls between particular dates....but! what am getting is a query that doesn't consider the ID (rid) as condition.

This is the query:

  SELECT * FROM booking 
  WHERE '2010-10-01' BETWEEN datefrom AND dateto - INTERVAL 1 DAY 
    OR '2010-10-09' BETWEEN datefrom + INTERVAL 1 DAY AND dateto 
    OR datefrom BETWEEN '2010-10-01' AND '2010-10-09' - INTERVAL 1 DAY 
   AND **rid = '5' 
   AND active = '1'** 
   LIMIT 0 , 30

This is the table structure for booking:

bid     gid      rid      datefrom        dateto          active
=================================================================
1       1        1        2010-09-16      2010-09-20      1
8       9        2        2010-09-06      2010-09-16      1
7       8        2        2010-09-23      2010-09-28      1

Answer 1

try this one

SELECT * FROM booking WHERE
('2010-10-01' BETWEEN datefrom AND dateto - INTERVAL 1 DAY) 
OR ('2010-10-09' BETWEEN datefrom + INTERVAL 1 DAY AND dateto) 
OR (datefrom BETWEEN '2010-10-01' AND '2010-10-09' - INTERVAL 1 DAY) 
AND rid = '5' AND active = '1' 
LIMIT 0 , 30

Answer 2

Try use brackets for date conditions:

  SELECT * FROM booking 
  WHERE rid = '5' 
   AND active = '1'
   AND ('2010-10-01' BETWEEN datefrom AND dateto - INTERVAL 1 DAY 
    OR '2010-10-09' BETWEEN datefrom + INTERVAL 1 DAY AND dateto 
    OR datefrom BETWEEN '2010-10-01' AND '2010-10-09' - INTERVAL 1 DAY) 
   LIMIT 0 , 30

Answer 3

I think you are going to need to use some parenthesis around all your OR conditions as a group so that it knows how to apply the AND conditions at the end.