MySQL Looking for free rooms in reservation system

Question

I have a little problem with finding free rooms in reservation system.
I found some solutions, but they do not seem to work.

My tables:

create table room
(
   roomID               int not null,
   maxPeople            numeric(2,0) not null,
   beds                 numeric(2,0) not null,
   primary key (roomID)
);

(
   reservationID        int not null,
   clientID             int,
   roomID               int,
   startDate            date not null,
   endDate              date not null,
   primary key (reservationID)
);

I tried something like this, but that's counting all rooms and totally ignore free rooms.

SELECT Id_pokoju 
from rezerwacje 
WHERE 
(
     (SELECT COUNT(ID_pokoju) 
     from rezerwacje 
     WHERE "2015-03-10" > Data_konca OR "2015-03-20" < Data_konca) 
     = 
     (SELECT COUNT(ID_pokoju) 
     from rezerwacje 
     GROUP BY Id_pokoju)
);

please rewrite your request with the same columns names as the tables you provided above. — Fares M., Mar 31 '15 at 10:47
So, you have 2 tables: rooms and reservations. And you want to find all the rooms, that don't have reservations on a certain period of time? Why are you comparing only the end date (Data_konca)? startDate is also required. — user4035, Mar 31 '15 at 11:06
possible duplicate of [Reservation system dates](http://stackoverflow.com/questions/10304552/reservation-system-dates) — Martin Bean, Mar 31 '15 at 11:15
This question has been answered time and time again. Please search first. — Martin Bean, Mar 31 '15 at 11:15
possible duplicate of [Find free room (booking system)](http://stackoverflow.com/questions/8779810/find-free-room-booking-system) — jpw, Mar 31 '15 at 11:24
I pasted wrong query in fact @user4035, there was startDate too. And sorry for that language mistakes — Rodzio, Apr 02 '15 at 18:38
@Rodzio Please, paste the correct query, and better prepare an sqlfiddle with test data, so I could see better what you want., and what you've got so far. — user4035, Apr 02 '15 at 20:31

score 1 · Accepted Answer · answered Mar 31 '15 at 11:10

To search for all rooms that have no bookings in a certain timeframe, consider the following:

SELECT roomID FROM room WHERE roomID NOT IN(
    SELECT roomID FROM reservation WHERE startDate < @EndDate AND endDate > @StartDate
)

What is happening there?

In the inner query, I am searching the reservation list for reservations that are at least partially inside the required timeframe, and get their room IDs. And then, in the outer query, I request from the room table all room IDs that are NOT listed, that is, they have no reservation that is at least partially inside the required timeframe.

@StartDate and @EndDate would have to be modified by you - you have to put your variables there.

MySQL Looking for free rooms in reservation system

1 Answers1

Linked