MySQL - 使用 INNER JOIN 显示 2 个日期之间的数据

Question

如何显示不在tblreserve表checkInDate和checkOutDate列范围内的房间？

客户

customerID | Fname | Lname
1          | John  | Smith

tbl预订

reservationID | customerID | reserveDate | checkInDate | checkOutDate
1             | 1          | 2015-09-09  | 2015-09-10  | 2015-09-11

卧室

roomID | roomTypeID | roomStatusID | roomNumber | floorNumber
1      | 1          | 1            | 101        | 1
2      | 2          | 1            | 102        | 1
3      | 3          | 1            | 103        | 1

tblroomreserve

roomID | reservationID
1      | 1

tbl房间状态

roomStatusID | roomStatus
1            | vacant
2            | occupied
3            | reserved

房间类型

roomTypeID | roomType
1          | Single Room
2          | Double Room
3          | Twin Room

我尝试了 INNER JOIN 但不是在 tblroom 表上显示在两个输入日期范围内未被占用的所有房间，它只显示在 tblreserve 不属于特定日期范围的表。即 '2015-09-12' 和 '2015-09-13'

SELECT
    `tblroom`.`roomNumber`
    , `tblroomtype`.`roomTypeDescription`
    , `tblroomtype`.`roomRate`
FROM
    `hotelreservation`.`tblreservation`
INNER JOIN `hotelreservation`.`tblcustomer` 
    ON (`tblreservation`.`customerID` = `tblcustomer`.`customerID`)
INNER JOIN `hotelreservation`.`tblroomreservation` 
    ON (`tblroomreservation`.`reservationID` = `tblreservation`.`reservationID`)
INNER JOIN `hotelreservation`.`tblroom` 
    ON (`tblroomreservation`.`roomID` = `tblroom`.`roomID`)
INNER JOIN `hotelreservation`.`tblroomstatus` 
    ON (`tblroom`.`roomStatusID` = `tblroomstatus`.`roomStatusID`)
INNER JOIN `hotelreservation`.`tblroomtype` 
    ON (`tblroom`.`roomTypeID` = `tblroomtype`.`roomTypeID`)
    AND NOT tblreservation.`checkInDate` >= '2015-09-12' AND tblreservation.`checkOutDate` <= '2015-09-13'

Answer 1

它应该适合你:

SELECT
  tblroom.roomNumber,
  tblroomtype.roomTypeDescription,
  tblroomtype.roomRate
FROM tblroom
INNER JOIN tblroomtype
  ON tblroom.roomTypeID = tblroomtype.roomTypeID
WHERE roomID NOT IN (SELECT
  tblroomreservation.roomID
FROM tblreservation
INNER JOIN tblroomreservation
  ON (tblroomreservation.reservationID = tblreservation.reservationID)
WHERE checkInDate BETWEEN '2015-09-12' AND '2015-09-13');

http://sqlfiddle.com/#!9/54d2a/5