Listing Available Rooms Query Help Needed
Posted: Thu Feb 17, 2005 8:40 am
I am making a hotel booking webpage and need some help.
Here are the tables involved.
What I want to do is to be able to search for rooms which are available for booking but not sure how to go about getting a list of the rooms without the ones that are booked at that time.
I have a query that will find ones that are booked but I'm not sure if I can use this but invert the results some how to get the other rooms or if I need a completly different approach.
Thanks for any help or ideas 
Here are the tables involved.
What I want to do is to be able to search for rooms which are available for booking but not sure how to go about getting a list of the rooms without the ones that are booked at that time.
I have a query that will find ones that are booked but I'm not sure if I can use this but invert the results some how to get the other rooms or if I need a completly different approach.
Code: Select all
SELECT booking.bookedfrom, booking.bookedto, room.number, room.type FROM booking, room, roomsbooked
WHERE
((booking.BookedTo > $arrival) AND (booking.BookedTo < $departure)
OR (booking.BookedFrom > $arrival) AND (booking.BookedFrom < $departure)
OR (booking.BookedFrom < $arrival) AND (booking.BookedTo > $arrival)
OR (booking.BookedFrom = $arrival)
OR (booking.BookedTo = $departure))
AND booking.bookingid = roomsbooked.bookingid
AND roomsbooked.roomid = room.roomid
AND room.type = $type