Joining 4 tables

Hi

I am trying to build search query for hotel rooms availability but it seems that his query is way over my head, and i need
help to build it. Note that there will be multiply hotels in the database.

Even that I am looking for available rooms, my idea was not to build availability table, but instead to use reservation
table, and I assume that if the room is not in the reservation table, it is available. (If you have any suggestions about this solution
I would like to hear it)

I have the following fields in the search form:

area (represented with areaid), checkInDate, checkOutDate, rooms (how many rooms he need), adults and children’s.

Here are the tables that should be involved in this search:

room
roomType
reservationroom
hotels

Here are the tables:


CREATE TABLE `room` (
  `roomID` int(11) NOT NULL AUTO_INCREMENT,
  `hotelID` int(11) NOT NULL,
  `roomtypeID` int(11) NOT NULL,
  `roomNumber` int(11) NOT NULL,
  `roomName` varchar(255) NOT NULL,
  `roomDescription` text,
  `roomVisible` tinyint(4) NOT NULL,
  PRIMARY KEY (`roomID`),
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

CREATE TABLE `roomtype` (
  `roomtypeID` int(11) NOT NULL AUTO_INCREMENT,
  `hotelID` int(11) NOT NULL,
  `roomtypeName` varchar(255) NOT NULL,
  `roomtypeAdults` int(11) NOT NULL,
  `roomtypeChildrens` int(11) NOT NULL,
  `roomtypeDescription` text,
  PRIMARY KEY (`roomtypeID`),
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

CREATE TABLE `hotel` (
  `hotelID` int(11) NOT NULL AUTO_INCREMENT,
  `areaID` int(11) NOT NULL,
  `hotelcategoryID` int(11) DEFAULT NULL,
  `hotelName` varchar(255) NOT NULL,
  `hotelShortDescription` text,
  `hotelAddress` varchar(255) DEFAULT NULL
  PRIMARY KEY (`hotelID`),
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;


CREATE TABLE `reservationroom` (
  `reservationroomID` int(11) NOT NULL AUTO_INCREMENT,
  `reservationID` int(11) NOT NULL,
  `hotelID` int(11) NOT NULL,
  `roomID` int(11) NOT NULL,
  PRIMARY KEY (`reservationroomID`)
) ENGINE=InnoDB DEFAULT CHARSET=greek;

Please note that I have removed the unnecessery fields from the tables, to make the code shorter and more easier to read.

Can anyone point me how to build this query to find available rooms? While I do know how to make simple join, this query is way over my head, and any help will be deeply appreciated.

Regards, zoreli

this is the best way to do it, and it’s not that hard

you query the existing reservations for the time span desired, and anything that comes up is not available, so you can go ahead and book any of the rooms that didn’t come up

querying for overlaps has been discussed here several times (search the forum for “timeline”)

here’s one example – http://www.sitepoint.com/forums/showthread.php?664040-Checking-a-number-from-a-range-contained-within-a-range-under-SQL

Thanks for your reply.

The pointed example was too much complcated for me to understand.

So far, I come out with following solution, but I will need to add some more tables here


 SELECT r.* FROM room r 
LEFT JOIN `reservationroom` rr 
ON r.`hotelID` = rr.`hotelID`
 AND r.`roomID` = rr.`roomID`
 WHERE ( rr.`reservationroomID` = '' OR rr.`reservationroomID` IS  NULL );

I am posting the solution here in hope that it may be valuable for someone else who have the similar problem. It is not final solution however.

Regards, zoreli

That also assumes the room is not unavailable for other reasons such as remodeling, maintenance, fumigating for bed bugs, being taken temporarily out of service, etc.