SQL-查找日期范围内的可用插槽

CREATE TABLE `property` (
    `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL
);
CREATE TABLE `venue` (
    `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `property_id` INT(11) NOT NULL,
    `name` VARCHAR(100) NOT NULL
);
CREATE TABLE `venue_available` (
    `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `venue_id` INT(100) NOT NULL,
    `day` VARCHAR(10) NOT NULL,
    `from_time` TIME NOT NULL,
    `to_time` TIME NOT NULL,
    `lead_time_in_minutes` INT(11)
);
CREATE TABLE `venue_unavailable` (
    `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `venue_id` INT(100) NOT NULL,
    `from_datetime` DATETIME NOT NULL,
    `to_datetime` DATETIME NOT NULL
);
CREATE TABLE `venue_reservation` (
    `id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `venue_id` INT(100) NOT NULL,
    `start_datetime` DATETIME NOT NULL,
    `end_datetime` DATETIME NOT NULL
);

我想在8月25日(星期六)到8月27日(星期一)上午10点到下午3点找到有场地的酒店

这是我尝试的SQL查询

SELECT
    p.id,
    p.name AS property_name,
    v.name AS venue_name
FROM
    venue v
LEFT JOIN
    property p ON v.property_id = p.id
-- venue_available
LEFT JOIN
    venue_available va_0 ON va_0.venue_id = v.id
LEFT JOIN
    venue_available va_1 ON va_1.venue_id = v.id
WHERE 1 = 1
-- venue_available
    AND (
        (va_0.day = 'sat' AND va_0.from_time <= '2018-08-25 10:00:00' AND va_0.to_time >= '2018-08-25 15:00:00') AND
        (va_1.day = 'sun' AND va_1.from_time <= '2018-08-26 10:00:00' AND va_1.to_time >= '2018-08-26 15:00:00')
    )
-- venue_unavailable
    AND NOT EXISTS (SELECT * FROM venue_unavailable vu WHERE '2018-08-25 10:00:00' <= vu.to_datetime AND '2018-08-26 15:00:00' >= vu.from_datetime)
GROUP BY
    p.id;

当前查询的问题是 venue_available venue_unavailable 它返回空结果,但是基于我期望的1个结果的数据。

这里是到SQL fiddle的链接,如果您想使用模式和fixture的话

http://sqlfiddle.com/#!9/33d60f/10

这就是我要做的
2。只有在一个或多个场地可用的情况下,才列出酒店

• 提供场地
• 场馆预订

你能帮我怎么做吗?十

谢谢您。

更新1

我按照下面的帖子来确定场馆不可用的重叠日期 Select rows that are not between dates (reservation)