Mysql query help

Databases
1

Hi to all,

I am developing a booking system .I have problem to get records .

Scenario is.

I have a database table where i define two fields arrival_date and departure_date.

I have a calendar when i move to month beneath the calendar i am showing all records of that month.

My query is

SELECT * FROM roombooking WHERE arrival_date between ‘2010-05-01’ and ‘2010-05-31’ or departure_date between ‘2010-05-01’ and ‘2010-05-31’

it show me all records whose arrival_date or departure_date is between ‘2010-05-01’ and ‘2010-05-31’

For example if a person book a room with arrival_date is ‘2010-04-01’ and departure date is ‘2010-06-01’

this records should also come in month of may because month of may is between april and june .

kindly help me

regards

2

here are all the ways that a booking can overlap the month of may –


                 2010-05-01            2010-05-31         
                       |                     |               
1 arrival---departure  |                     |               
                       |                     |               
2             arrival--|--departure          |               
                       |                     |               
3                      | arrival---departure |               
                       |                     |               
4            arrival---|---------------------|---departure       
                       |                     |               
5                      |            arrival--|--departure 
                       |                     |               
6                      |                     |  arrival---departure

with me so far?

your query is successful for cases 2, 3, and 5, but you are missing case 4, right?

the easiest way to include it is like this –


... WHERE departure >= '2010-05-01'  /* eliminates case 1 */
      AND arrival   <= '2010-05-31'  /* eliminates case 6 */

notice that it’s an AND which means both of those have to be true

3

Thanks r937

Yes i missed 4th point .

thanks again