Join 3 tables - i just can't do it right

Databases
1

I have 3 tables, that i want to join, one is room the second is reservation and the third is is reservationroom

Here are my tables structure:

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,
   `roomName_en` varchar(255) NOT NULL,
   `roomDescription` text,
   `roomDescription_en` text,
   `roomSorder` int(11) NOT NULL,
   `roomVisible` tinyint(4) NOT NULL,
   PRIMARY KEY (`roomID`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;


CREATE TABLE `reservation` (
    `reservationID` int(11) NOT NULL AUTO_INCREMENT,
    `customerID` int(11) NOT NULL,
    `hotelID` int(11) NOT NULL,
    `reservationCreatedOn` datetime NOT NULL,
    `reservationCreatedFromIp` varchar(255) CHARACTER SET greek NOT NULL,
    `reservationNumberOfAdults` tinyint(4) NOT NULL,
    `reservationNumberOfChildrens` tinyint(4) NOT NULL,
    `reservationArrivalDate` date NOT NULL,
    `reservationDepartureDate` date NOT NULL,
    `reservationCustomerComment` text CHARACTER SET greek,
    PRIMARY KEY (`reservationID`)
) ENGINE=InnoDB AUTO_INCREMENT=47 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 AUTO_INCREMENT=47 DEFAULT CHARSET=utf8;

The reason why i have the reservationroom table is simple, one reservation can have more than one room.

What i am trying to do? I would like to check if the room is available for certain period.

Here is my current query (which produce wrong results):

SELECT * FROM reservation re 
LEFT JOIN reservationroom rr
ON re.reservationID = rr.reservationID
LEFT JOIN room r
ON rr.roomID = r.roomID
WHERE NOT (re.reservationArrivalDate BETWEEN '2014-07-07' AND '2014-07-10' )
AND NOT (re.reservationDepartureDate BETWEEN '2014-07-07' AND '2014-07-10' )
AND re.hotelID = 14
GROUP BY rr.roomID

Can anyone tell me what i am doing wrong? Hotel with id 14 has only one reservation for this period, and hotel has 6 rooms, so i should get 5 results, and I am getting none (0).

Any help will be deeply appreciated.

Regards, z0reli

2

First of all, you’ve got some extraneous fields in your tables - they can be extrapolated by queries. For example, you only need hotelID on the room table. You’ll never have a roomID with two hotels.

But I think this will give you a list of rooms for a hotel that are available for a specified period of time:

SELECT r.*
  FROM room r
  LEFT JOIN (SELECT rr.roomID, re.reservationID 
	       FROM reservationroom rr 
	       LEFT JOIN reservation re ON re.reservationID = rr.reservationID
	      WHERE re.reservationArrivalDate BETWEEN '2014-07-07' AND '2014-07-10'
	        AND re.reservationDepartureDate BETWEEN '2014-07-07' AND '2014-07-10') sq ON sq.roomID = r.roomID
 WHERE re.hotelID = 14
   AND sq.reservationID IS NULL
3

Hi
Thanks for trying to help me. I just test the query that you posted and I get error, unknown column re.hotelID in where clausule

Regards, z0reli

4

Sorry - should be r.hotelID

5

Thanks a lot. Query now works however,i have one doubt about the between clausule…

I have one record in reservation from hotelID = 14 where reservationArrivalDate is ‘2014-07-10’. In the above query, I get this room as available…is this correct behavior, or i should change the query and use >= instead of between?

Regards, z0reli

6

That shouldn’t be if the fields are date and not datetime.

7

Hi
Fields are date…I just check that. I am confused now…

Regards, z0reli

8

They are date while you have created
reservationCreatedOn datetime NOT NULL,
?

9

That field is not included in the query, so it is irrelevant i think…

10