MySQL - 如果甚至一個外鍵都不符合條件，則排除結果（日期可用性檢查器）

我有三張表，一張列出地區，一張列出酒店，另一張列出預訂。Hotels 表具有區域表的外鍵，以顯示哪個酒店位於哪個區域內，而預訂表具有酒店表的外鍵，以列出對酒店的預訂。

我正在嘗試使您可以輸入日期，並且如果該日期不可用，則不會在結果中返回酒店（儘管也可以不輸入日期）。

目前，使用頁面底部列出的查詢，它會列出所有酒店，即使他們沒有預訂。此時，只有一間酒店有任何預訂，而此時一共有兩間預訂。在我加入“DISTINCT”條款之前，為了不列出重複的條目，它會列出所有酒店，包括有預訂的酒店，除了這家酒店，它會列出兩次，因為有兩次預訂。現在，使用 DISTINCT 子句，它只會顯示一個結果，但它總是會顯示一個結果。它本質上是說“這個條目在指定的時間範圍內，所以我們不能顯示，但另一個不在，所以我們會顯示它”，即使它是同一家酒店。

作為參考，我目前的查詢，使用 2020-09-02 作為檢查可用性的日期；

SELECT DISTINCT hotels.hotel_name FROM hotels

LEFT JOIN reservations ON hotels.id=reservations.hotel_id

LEFT JOIN regions ON hotels.region_id=regions.id WHERE Cast('2020-09-02' as DateTime) NOT BETWEEN reservations.start_date AND reservations.end_date OR reservations.start_date is NULL OR reservations.end_date is NULL;

目前結果（有可用日期*）*；

Hotel 1 with two reservations
Hotel 2
Hotel 3
Hotel 4
Hotel 5

目前結果（日期不可用）；

Hotel 1 with two bookings
Hotel 2
Hotel 3
Hotel 4
Hotel 5

目前結果（日期可用，但未使用“DISTINCT” ）；

Hotel 1 with two bookings
Hotel 1 with two bookings
Hotel 2
Hotel 3
Hotel 4
Hotel 5

目前結果（日期不可用，但未使用“DISTINCT” ）；

Hotel 1 with two bookings
Hotel 2
Hotel 3
Hotel 4
Hotel 5

預期結果（如果選擇的日期不可用）；

Hotel 2
Hotel 3
Hotel 4
Hotel 5

預期結果（如果選擇的日期可用）；

Hotel 1 with two bookings
Hotel 2
Hotel 3
Hotel 4
Hotel 5

希望預期的結果場景可以讓我了解我的目標。

任何幫助表示讚賞。

編輯：連結到問題的 DBFiddle：https ://www.db-fiddle.com/f/n9Ew6nLCWQHDjYfxzp49xB/16

由於 n:m 關係，您需要一個 DISTINCT 但是我會寫不同的日期選擇，所以應該排除所有 holtel.id ，在那天有保留，這樣就不能再選擇了

架構（MySQL v5.7）

CREATE TABLE `reservations` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `hotel_id` int(10) unsigned DEFAULT NULL,
 `start_date` date DEFAULT NULL,
 `end_date` date DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


insert  into `reservations`(`id`,`hotel_id`,`start_date`,`end_date`) values
   (1,1,'2020-08-26','2020-09-02'),
   (2,1,'2020-12-06','2020-12-13');


CREATE TABLE `regions` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `region_name` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert  into `regions`(`id`,`region_name`) values
   (1,'Region 1'),
   (2,'Region 2'),
   (3,'Region 3'),
   (4,'Region 4'),
   (5,'Region 5');


CREATE TABLE `hotels` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `region_id` int(10) unsigned DEFAULT NULL,
 `hotel_name` varchar(255) DEFAULT NULL,

 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert  into `hotels`(`id`,`region_id`,`hotel_name`) values
   (1,1,'Hotel 1'),
   (2,3,'Hotel 2'),
   (3,5,'Hotel 3'),
   (4,5,'Hotel 4'),
   (5,4,'Hotel 5');

---

查詢 #1

SELECT 
   DISTINCT hotels.hotel_name, regions.region_name
FROM
   hotels
       LEFT JOIN
   reservations ON hotels.id = reservations.hotel_id
       LEFT JOIN
   regions ON hotels.region_id = regions.id
WHERE
(reservations.start_date is NULL OR reservations.end_date is NULL)

       OR hotels.id NOT IN
       (SELECT h.id 
FROM hotels h
   INNER JOIN 
   reservations r ON h.id = r.hotel_id
WHERE
  CAST('2020-09-02' AS DATE) BETWEEN r.start_date AND r.end_date)  
;

---

在 DB Fiddle 上查看

引用自：https://dba.stackexchange.com/questions/287012