Check availability of rooms available

I have 3 table in my sql database:

Table Room

roomnumber   roomtype    
  1           DeluxeRoom           
  2           DeluxeRoom  
  3           DeluxeRoom
  100         StandardRoom           
  101         StandardRoom  
  102         StandardRoom

Room is a table which store the room and their type.

Table Reservation

reservationid  checkin       checkout
  1           2017-01-01     2017-01-03
  2           2017-01-10     2017-01-20
  3           2017-02-05     2017-02-08

Reservation is a table where users make their booking.

Table RoomReserve

reservationid    roomnumber
 1                 1
 2                 100
 2                 101
 3                 1
 3                 2
 3                 3

In RoomReserve, for each reservationid, I can input multiple values for roomnumber. I can also input same roomnumber for different reservationid if the checkin and checkout date are not the same.

I am trying to make a query to count the available rooms when user want to book rooms but the results i am getting is wrong.

Here are the codes i am trying:

 1) SELECT COUNT(roomnumber) FROM room WHERE roomtype='DeluxeRoom'  AND roomnumber NOT IN ( 
SELECT * FROM roomreserve r,reservation e, room m  WHERE r.reservation_id=e.reservation_id AND m.roomnumber=r.roomnumber
AND ('$checkin' BETWEEN start_date AND end_date) OR ('$checkout' BETWEEN start_date AND end_date)); 

2) SELECT COUNT(roomnumber) FROM room WHERE roomtype='DeluxeRoom'  AND roomnumber NOT IN ( 
SELECT COUNT(roomnumber) FROM roomreserve r,reservation e, room m  WHERE r.reservation_id=e.reservation_id AND m.roomnumber=r.roomnumber
AND ('$checkin' BETWEEN start_date AND end_date) OR ('$checkout' BETWEEN start_date AND end_date));

have a look at this diagram

             requested reservation --
             $checkin   $checkout           
                |          |
                |          |
1.  S------E    |          |
                |          |
                |          |
2.          S------E       |
                |          |
                |          |
3.              | S------E |
                |          |
                |          |
4.          S----------------------E
                |          |
                |          |
5.              |      S------E
                |          |
                |          |
6.              |          |    S------E

    existing reservations --
    S=checkin   E=checkout

what you want is to find all the rooms that are ~not~ already reserved in the requested $checkin-$checkout range

this means take all rooms and subtract the ones that are involved in cases 2 through 5 above

how to find cases 2 through 5 above? that’s easily done like this –

WHERE E >= $checkin   /* eliminates case 1 */
  AND S <= $checkout  /* eliminates case 6 */

so you need a LEFT OUTER JOIN from all possible rooms on the left table, with cases 2-5 as the right table, and then do an IS NULL check to find those not matched

SELECT room.roomnumber FROM room LEFT OUTER JOIN ( SELECT roomreserve.roomnumber , reservation.checkin , reservation.checkout FROM roomreserve INNER JOIN reservation ON reservation.reservationid = roomreserve.reservationid ) AS v ON v.roomnumber = room.roomnumber AND v.checkout >= $checkin /* eliminates case 1 */ AND v.checkin <= $checkout /* eliminates case 6 */ WHERE v.roomnumber IS NULL
this lists the rooms so you can check the accuracy of the query… convert to COUN(*) when you’re comfortable that it works

p.s. if it doesn’t work, perhaps give some more elaborate test data, i did not actually test it

hey @arshad4566, did you try my query?

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.