mysql - 如何连接列不应该存在或查询值不应该在日期之间的表？

Question

我知道我的标题不是最好的。我试图完成的是:

我有 4 张 table :

• parking 位
• 地址
• 可用性
• 预订

我的查询需要 3 个参数:

• 邮政编码
• 从(所需的开始日期(时间))
• 至(所需结束日期(时间))

首先我们过滤 parking 位

INNER JOIN `deb93093_api`.`Addresses`
    ON (
      `deb93093_api`.`Parking_Spots`.`address` = `Addresses`.`uuid`
    )

===

WHERE (`Addresses`.`zipCode` = '4651AA')

然后是可用的 parking 位

INNER JOIN `deb93093_api`.`Availability`
    ON (
      `Availability`.`parkingSpot` = `Parking_Spots`.`uuid`
    )

===

AND (
     '2016-12-31' BETWEEN `Availability`.`fromDate` AND `Availability`.`toDate`
  )
AND (
    '2016-12-31' BETWEEN `Availability`.`fromDate` AND `Availability`.`toDate`
  )

但问题是:

我需要获取预订中尚未预订的所需时间的 parking 位

-- AND (
 --   '2016-12-31' NOT BETWEEN `Bookings`.`from` AND `Availability`.`to`
 -- )

 -- AND (
 --   '2016-12-31' NOT BETWEEN `Bookings`.`from` AND `Availability`.`to`
 -- )

但是内部联接只会尝试显示有记录的 parking 位。然而，他们也应该展示那些没有预订的人。有我可以使用的连接类型吗？我想选择可用的 parking 位，即

available = parkingspots_with_right_zipcode - 仅可用 - 车主设置为可用但已预订的 parking 位

到目前为止的完整查询:

SELECT
  `Parking_Spots`.*
FROM
  `deb93093_api`.`Parking_Spots`

  INNER JOIN `deb93093_api`.`Addresses`
    ON (
      `deb93093_api`.`Parking_Spots`.`address` = `Addresses`.`uuid`
    )
  INNER JOIN `deb93093_api`.`Availability`
    ON (
      `Availability`.`parkingSpot` = `Parking_Spots`.`uuid`
    )

  OUTER JOIN `deb93093_api`.`Bookings`
   ON (
     `Bookings`.`parkingSpot` = `Parking_Spots`.`uuid`
   )



 WHERE (`Addresses`.`zipCode` = '4651AA')
  AND (
     '2016-12-31' BETWEEN `Availability`.`fromDate` AND `Availability`.`toDate`
  )
  AND (
    '2016-12-31' BETWEEN `Availability`.`fromDate` AND `Availability`.`toDate`
  )

 -- AND (
 --   '2016-12-31' NOT BETWEEN `Bookings`.`from` AND `Availability`.`to`
 -- )

 -- AND (
 --   '2016-12-31' NOT BETWEEN `Bookings`.`from` AND `Availability`.`to`
 -- )

从和到都是预订，所以我想过滤那些已经预订了所需时间的人。但是，如果预订中没有该 parking 位的记录，它仍应显示该 parking 位，因为它未预订(因此可用)

进一步澄清的伪代码

 (
AND (
WHERE $desired_from NOT BETWEEN Bookings`.`from` AND `Bookings`.`to` )

  AND (
    $desired_to NOT BETWEEN `Bookings`.`from` AND `Bookings`.`to`
  )
OR
WHERE [there is no record in Bookings]

Answer 1

在这种情况下，您可以在连接ON子句中使用带有附加条件的LEFT JOIN，然后仅过滤未找到连接记录的行:

SELECT
  `Parking_Spots`.*
FROM
  `deb93093_api`.`Parking_Spots`

  INNER JOIN `deb93093_api`.`Addresses`
    ON (
      `deb93093_api`.`Parking_Spots`.`address` = `Addresses`.`uuid`
    )
  INNER JOIN `deb93093_api`.`Availability`
    ON (
      `Availability`.`parkingSpot` = `Parking_Spots`.`uuid`
    )

  LEFT JOIN `deb93093_api`.`Bookings`
   ON (
     `Bookings`.`parkingSpot` = `Parking_Spots`.`uuid`
   ) 
   AND (
     '2016-12-31' BETWEEN `Bookings`.`from` AND `Bookings`.`to`
   )

 WHERE (`Addresses`.`zipCode` = '4651AA')
  AND (
     '2016-12-31' BETWEEN `Availability`.`fromDate` AND `Availability`.`toDate`
  )
  AND `Bookings`.`uuid` IS NULL -- THIS MEANS THE SPOT IS NOT BOOKED