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));