mysql - 我的加入返回 0 个结果

Question

我必须遵循以下 3 个表:房间、预订和预订房间

它们的结构如下:

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=46 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=46 DEFAULT CHARSET=utf8;

(请注意，为了简单起见，外键已从 create 语句中删除)

我想做的事情:我想要获取所有未在特定日期预订的房间，即仅特定酒店的免费房间(我有其ID)

这是我现在的查询:

SELECT r.* FROM room r  
LEFT JOIN `reservationroom` rr 
ON r.`hotelID` = rr.`hotelID` 
AND r.`roomID` = rr.`roomID`
LEFT JOIN `reservation` re
ON rr.`reservationID` = re.`reservationID`
WHERE (rr.`reservationroomID` = '' 
    OR rr.`reservationroomID` IS  NULL 
    AND re.`reservationArrivalDate` >= 2014-08-27
    AND re.`reservationDepartureDate` <= 2014-08-29 
    AND r.`hotelID` = 10 
    AND r.`roomVisible` = 1);

此查询现在返回 0 个结果。它应该返回 9 条记录，因为 ID = 10 的酒店有 9 个空闲房间(预订表中不存在特定日期的预订)

有人可以帮我解决这个问题吗？我试图用几个小时来解决这个问题，但没有成功。

Answer 1

您正在使用左连接，因此除第一个表之外的所有表的条件都应位于on 子句中。我认为您想要一个更像这样的查询:

SELECT r.*
FROM room r LEFT JOIN
    `reservationroom` rr 
     ON r.`hotelID` = rr.`hotelID` AND
        r.`roomID` = rr.`roomID` LEFT JOIN
    `reservation` re
      ON rr.`reservationID` = re.`reservationID` AND
         re.`reservationArrivalDate` >= 2014-08-27 AND
         re.`reservationDepartureDate` <= 2014-08-29 
WHERE r.`hotelID` = 10 AND r.`roomVisible` = 1 AND re.reservationID is null;

我不确定与空字符串的比较是什么。似乎没有必要用于此目的。