SQL Query Hotel Room from two tables (Type and Availability)

1
votes

First table 'Booking' contains From_date, To_date, and Room_ID.

Second table 'Room' contains Room_ID and a random column Room_Floor.

 Booking
 From_date || To_date || Room_ID

 Room
 Room_ID || Room_Floor

a) What query would result in booked rooms that are in the 1st floor, from a specific date to a specific one.

b) What query would result in available rooms that are in 2nd floor for example, from a specific date to a specific one.

Thank you in advance. Any help is greatly appreciated.

Using MS SQL 2008.

2
sqlsql-server
Have you tried anything? Are there any specific problems? - Onkel Toob
and let's say Room_Floor, does this mean you haven't even created the tables yet? - Christian Phillips
Of course I have created the tables. But the Room table has many columns, such as Room_Class (economic or VIP), Room_Beds (number of beds), Room_Position (west or east), and among other columns there is also Room_Floor. I stated "let's say" just to emphasize a random column of the Room table. Thank you. - user3707554

2 Answers

2
votes
`SELECT * from Room R
INNER JOIN Booking B on B.Room_ID = R.Room_ID
where Room_Floor = 1
AND From_date BETWEEN GETDATE() AND To_date
`

This will find all bookings for rooms on Floor 1

`SELECT * from Room R
where not exists (select * from bookings where Room_ID = R.RoomID and GETDATE()
Between From_date AND To_date)
and Room_Floor = 2`

This will find all available rooms on floor 2

Something like that I think

0
votes

Great thanks to user2270653. This is how I solved the query using user2270653 guidance.

Booking rooms Floor 1:

SELECT [Room_Number], [Room_Floor], [Room_Position], [Room_Class], 
[Room_Beds], [Room_Price] FROM [Room]  INNER JOIN [Booking] 
ON Booking.Room_ID = Room.Room_ID
WHERE [Booking_To]  >= '2014/05/20' 
AND [Booking_From]  <= '2014/07/25'
AND [Room_Floor]='1'

Available rooms Floor 2:

SELECT [Room_ID], [Room_Number], [Room_Floor], [Room_Position], [Room_Class],    
[Room_Beds], [Room_Price] FROM [Room] where not exists (SELECT [Room_ID] 
FROM [Booking] WHERE Booking.Room_ID = Room.Room_ID 
AND [Booking_To]  >= '2014/05/20' 
AND [Booking_From] <= '2014/07/25')
AND [Room_Floor]='2'