codejava codejava - 1 year ago 78
MySQL Question

Select specific records between two dateranges

I am trying to get unreserved hotel rooms according to a daterange which is given by user.

I have two tables

  • rooms with columns room_id, roomno

  • reservations with columns reserv_id, room_id, checkindate, checkoutdate

Im applying this query

$sql = "SELECT DISTINCT roomno
FROM rooms
LEFT JOIN reservation ON rooms.room_id = reservation.room_id
(reservation.checkindate > '$newreservcheckin')
(reservation.checkindate > '$newreservcheckout')
(reservation.checkoutdate < '$newreservcheckin')
(reservation.checkoutdate < '$newreservcheckout')

$newreservcheckin and $newreservcheckout are date ranges provided by user

Let's say we have rooms with roomno as 100 and 101
now 100 is booked from 9-16-2016 to 9-18-2016
and 100 is also booked from 9-27-2016 to 9-29-2016
and room 101 is unreserved(meaning it has no record in reservation table)

Suppose if user gives dates 9-26-2016 to 9-30-2016 query will ignore the room no 100 entry which is from 9-27-2016 to 9-29-2016 but will show the room no 100 with dates from 9-16-2016 to 9-18-2016 and will show 101 room

How to mold the query so that it may not give the same room but the one with different dates?

Answer Source

You need to select the rooms which are either (A) Not booked or (B) Booked but outside the range you are querying for, below query should work:

select no from room r
where not exists (
  select id from reservation where room_id =


select no from room r
where not in (
  select distinct room_id from reservation
        checkindate between '2016-09-20' and '2016-09-22'
        checkoutdate between '2016-09-20' and '2016-09-22'

Here is the SQL Fiddle.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download