CODEWITHSUNDEEP
sql
Obak Shibly
Obak Shibly

Reputation: 65

sql query for hotel room reservation

I am making a hotel reservation system in windows form. Users book a hotel room by selecting the hotel ID and by giving a check_in and check_out date. Now I want to retrieve the room number (room_no) from tblRoom where they are not in tblReservation (I mean the rooms that are not booked yet), and also the room number (room_no) of those that are in tblReservation but not between the check_in and check_out date. The below code allows me to get the room_id, but I need the 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:

enter image description here

Upvotes: 4

Views: 11033

Answers (1)

Blag
Blag

Reputation: 5894

The Query #2 give you all the free rooms for a given hotel_id and a check in/check out date (the in and out date are included, for 05/n to 10/n you stay 6days)

The query #3 will give you all rented room with the previous parameters.

SQL Fiddle

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

Query 1:

set @hotel_id = 1, @check_in = '2017-04-03', @check_out = '2017-04-05'

Query 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 |

Upvotes: 5

Related Questions