By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,300 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,300 IT Pros & Developers. It's quick & easy.

Find the rooms in a given hotel which is unoccupied

P: 19
Hi, experts:
I've got a question that I want to find all the rooms in a given hotel which is unoccupied and I write the query like that, but it cannot work. can anyone help me to figure out? thank you and waiting for your reply.

the schema for the four tables look like that
hotel(hotelNo,hotelName,city)
room(roomNo,hotelNo,type,price)
booking(hotelNo,guestNo,dateFrom,dateTo,roomNo)
guest(guestNo,guestName,guestAddress)

select roomNo, hotelNo
from room
where
(hotelNo in (select hotelNo from hotel where hotelName='Grosvenor')
and
(roomNo,hotelNo not in (select roomNo,hotelNo from booking where dateFrom<current_date and dateTo>current_date))
Nov 11 '08 #1
Share this Question
Share on Google+
2 Replies


100+
P: 310
Hi, experts:
I've got a question that I want to find all the rooms in a given hotel which is unoccupied and I write the query like that, but it cannot work. can anyone help me to figure out? thank you and waiting for your reply.

the schema for the four tables look like that
hotel(hotelNo,hotelName,city)
room(roomNo,hotelNo,type,price)
booking(hotelNo,guestNo,dateFrom,dateTo,roomNo)
guest(guestNo,guestName,guestAddress)


select roomNo, hotelNo
from room
where
(hotelNo in (select hotelNo from hotel where hotelName='Grosvenor')
and
(roomNo,hotelNo not in (select roomNo,hotelNo from booking where dateFrom<current_date and dateTo>current_date))
I don't understand the part about your date range, so I am just basically repeating yours without knowing if it will work logically, but you want something similar to the following:

Expand|Select|Wrap|Line Numbers
  1. select t3.guestNo, t3.dateFrom, t3.dateTo, t1.hotelNo, t2.roomNo
  2. from hotel t1 inner join room t2 on t1.hotelNo=t2.hotelNo
  3. left join booking t3 on (t1.hotelNo=t3.hotelNo and t2.roomNo=t3.roomNo)
  4. where t3.dateFrom>current_date and t3.dateTo<current_date and t3.guestNo is NULL
  5.  
What you are doing is joining three of the four tables. The first join between hotel and room is just an inner join and basically collects the information about the essential attribute pairs of what rooms are in the various hotels.

The next join is a "left" join. It will add to the first join the information about the bookings within the dates of interest for the corresponding hotel-room pairs "if it exists". This is why you use a left join here. If the information for a particular combination of hotel+room+daterange does not exist, then the columns for that table will have the value of NULL, and this can be found for example by looking where the guestNO is NULL.

I have not tried this, and my logic is not always 100% correct, so I do not guarantee this will work, but this is probably the direction you want to go in.
Nov 12 '08 #2

P: 19
Thank you very much for your reply and it works.
Nov 16 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.