Unanswered: MySQL Expert Required!! Advanced Query Problem
I am currently developing a hotel booking system for my University final year project and am having some serious problems with my query to calculate room availability.
I felt the best method to calculate room availability was to first calculate which rooms were already booked for any specific queried dates and then to subtract those results from the list of total rooms. That would then return which rooms were available on those dates.
However the query that I am using to calculate which rooms are already booked is very inefficient, in that, for example,
A booking which is for more than one night can overlap the date you are testing for availability. It's even worse when you check the availability of rooms for multiple nights. My query does not cover those scenarios.
If anyone can help me solve this problem or suggest alternative methods I would be most appreciative.
Below is the MySQL code for the relevant parts of my database, and the query that I am currently using.
CREATE TABLE booking
booking_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
e_mail VARCHAR(40) NOT NULL REFERENCES guestdetails(e_mail),
arrival_date DATE NOT NULL,
departure_date DATE NOT NULL,
CREATE TABLE roombooked
booking_id INTEGER UNSIGNED NOT NULL REFERENCES booking(booking_id),
room_id INTEGER UNSIGNED NOT NULL REFERENCES rooms(room_no),
no_of_nights INTEGER UNSIGNED NOT NULL,