Sql request for hotel room booking
I am making a hotel booking system in the form of a window. Users book a hotel room by choosing a hotel identification number and giving the date check_in
and check_out
. Now I want to get the room number ( room_no
) from tblRoom
where they are not in tblReservation
(I mean rooms that have not yet been booked), as well as the room number ( room_no
) of those who are in tblReservation
, but not between the date check_in
and check_out
. The following code allows me to get room_id
, but I need it room_no
.
SqlCommand cmd = new SqlCommand(@"SELECT room_id FROM tblRoom WHERE (hotel_id=@hotel_id AND
room_id NOT IN (SELECT room_id FROM tblReservation)) union select room_id from tblReservation
where (@endDate<check_in or @startDate>check_out) and hotel_id=@hotel_id", con);
Here are my tables:
source to share
Request # 2 provides you with all available rooms for a given date hotel_id
and check in
/ check out
(date in
and out
included for 05 / n to 10 / n you stay 6 days)
Request # 3 will provide you with the entire rented room with the previous parameters.
MySQL 5.6 Schema setup :
CREATE TABLE TblReservation
(`reservation_id` int, `hotel_id` int, `room_id` int, `check_in` date, `check_out` date)
;
INSERT INTO TblReservation
(`reservation_id`, `hotel_id`, `room_id`, `check_in`, `check_out`)
VALUES
(1, 1, 1, '2017-04-01', '2017-04-02'),
(2, 1, 1, '2017-04-06', '2017-04-10'),
(3, 1, 2, '2017-04-01', '2017-04-03'),
(4, 1, 4, '2017-04-01', '2017-04-10'),
(5, 2, 5, '2017-04-01', '2017-04-10')
;
CREATE TABLE TblRoom
(`room_id` int, `hotel_id` int, `room_num` int)
;
INSERT INTO TblRoom
(`room_id`, `hotel_id`, `room_num`)
VALUES
(1, 1, 1100),
(2, 1, 1200),
(3, 1, 1300),
(4, 1, 1400),
(5, 2, 2500)
;
Request 1 :
set @hotel_id = 1, @check_in = '2017-04-03', @check_out = '2017-04-05'
Request 2 :
select TblRoom.*
from TblRoom
left join TblReservation
on TblRoom.hotel_id = TblReservation.hotel_id
and TblRoom.room_id = TblReservation.room_id
and TblReservation.check_out >= @check_in
and TblReservation.check_in <= @check_out
where
TblRoom.hotel_id = @hotel_id
and TblReservation.reservation_id IS NULL
Results :
| room_id | hotel_id | room_num |
|---------|----------|----------|
| 1 | 1 | 1100 |
| 3 | 1 | 1300 |
Query 3 :
select
TblRoom.*,
date_format(check_in,'%Y-%m-%d') check_in,
date_format(check_out,'%Y-%m-%d') check_out
from TblRoom
inner join TblReservation
on TblRoom.hotel_id = TblReservation.hotel_id
and TblRoom.room_id = TblReservation.room_id
and TblReservation.check_out >= @check_in
and TblReservation.check_in <= @check_out
where
TblRoom.hotel_id = @hotel_id
Results :
| room_id | hotel_id | room_num | check_in | check_out |
|---------|----------|----------|------------|------------|
| 2 | 1 | 1200 | 2017-04-01 | 2017-04-03 |
| 4 | 1 | 1400 | 2017-04-01 | 2017-04-10 |
source to share