Find the rooms in a given hotel which is unoccupied

[rubyhuang]

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,h otelName,city)
room(roomNo,hot elNo,type,price )
booking(hotelNo ,guestNo,dateFr om,dateTo,roomN o)
guest(guestNo,g uestName,guestA ddress)

select roomNo, hotelNo
from room
where
(hotelNo in (select hotelNo from hotel where hotelName='Gros venor')
and
(roomNo,hotelNo not in (select roomNo,hotelNo from booking where dateFrom<curren t_date and dateTo>current_ date))

[coolsti]

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,h otelName,city)
room(roomNo,hot elNo,type,price )
booking(hotelNo ,guestNo,dateFr om,dateTo,roomN o)
guest(guestNo,g uestName,guestA ddress)


select roomNo, hotelNo
from room
where
(hotelNo in (select hotelNo from hotel where hotelName='Gros venor')
and
(roomNo,hotelNo not in (select roomNo,hotelNo from booking where dateFrom<curren t_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:

select t3.guestNo, t3.dateFrom, t3.dateTo, t1.hotelNo, t2.roomNo
from hotel t1 inner join room t2 on t1.hotelNo=t2.hotelNo
left join booking t3 on (t1.hotelNo=t3.hotelNo and t2.roomNo=t3.roomNo)
where t3.dateFrom>current_date and t3.dateTo<current_date and t3.guestNo is NULL

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+date range 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.

[rubyhuang]

Thank you very much for your reply and it works.