CODEWITHSUNDEEP
mysql
Rodzio
Rodzio

Reputation: 15

MySQL Looking for free rooms in reservation system

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

Upvotes: 0

Views: 467

Answers (1)

Alexander
Alexander

Reputation: 20224

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.

Upvotes: 1

Related Questions