Problem with MySQL query

Question

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

Michael Pakhantsov · Accepted Answer · 2010-09-17 06:16:53Z

3

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

answered Sep 17, 2010 at 6:16

Michael Pakhantsov

25.5k7 gold badges63 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

abatishchev Over a year ago

@majimoto: Don't forget to accept correct answer (using tick)

rahim asgari · Accepted Answer · 2010-09-17 06:16:23Z

0

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

answered Sep 17, 2010 at 6:16

rahim asgari

12.4k10 gold badges45 silver badges53 bronze badges

1 Comment

majimoto Over a year ago

Has the same problem as my query...but Michael Pakhantsov's solution works fine

Andrew Barber · Accepted Answer · 2010-09-17 06:17:47Z

0

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.

answered Sep 17, 2010 at 6:17

Andrew Barber

40.3k20 gold badges97 silver badges124 bronze badges

Collectives™ on Stack Overflow

Problem with MySQL query

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related