mysql - 我从 mysql join 查询得到错误的结果

Question

我有 2 张 table ，我想加入，一张是房间，另一张是预订。

基本上，我想搜索未预订的房间(不在预订表中)，并从房间表中获取这些房间(不在预订表中)的详细信息。

这是我的表格结构:

 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;

这是我现在的查询，它给出了错误的结果:

SELECT * FROM room r 
LEFT JOIN reservation re
ON r.hotelID = re.hotelID
WHERE re.hotelID = 13
AND NOT 
(re.reservationArrivalDate >= '2014-07-07' AND  re.reservationDepartureDate <= '2014-07-13')

我还创建了一个 fiddle ，其中包含两个表中的数据: http://sqlfiddle.com/#!2/4bb9ea/1

任何帮助将不胜感激

问候，约翰

Answer 1

我同意房间号被遗漏了，但查询模板应该看起来像

SELECT 
  * 
FROM
  room r 
  LEFT JOIN reservation re 
    ON r.hotelID = re.hotelID 
WHERE r.hotelID = 2 
  AND NOT (
    re.hotelID IS NOT NULL 
    AND re.reservationArrivalDate >= '2014-07-07' 
    AND re.reservationDepartureDate <= '2014-09-23'
  ) ;

您需要将 where 语句中的表从预订更改为房间。另外，您还需要将 re.hotelID 添加到 where 语句，因为在 where 语句中您需要检查记录是否为空，并且仅在尝试检查日期之后