mysql - 帮助形成 mysql 查询以查找给定日期范围内的免费(可用) field /资源

Question

我有这样的表格和数据:

venues table contains : id
+----+---------+| id | name    |+----+---------+|  1 | venue 1 ||  2 | venue 2 |---------------event_dates : id, event_id, event_from_datetime, event_to_datetime, venue_id+----+----------+---------------------+---------------------+----------+| id | event_id | event_from_datetime | event_to_datetime   | venue_id |+----+----------+---------------------+---------------------+----------+|  1 |        1 | 2009-12-05 00:00:00 | 2009-12-07 00:00:00 |        1 ||  2 |        1 | 2009-12-09 00:00:00 | 2009-12-12 00:00:00 |        1 ||  3 |        1 | 2009-12-15 00:00:00 | 2009-12-20 00:00:00 |        2 |+----+----------+---------------------+---------------------+----------+

这是我的要求:我想要在 2009-12-06 00:00:00 免费的 field 即

我应该得到

| field 编号|

|2 |

目前我有以下查询，

select ven.id , evtdt.event_from_datetime, evtdt.event_to_datetime from venues ven left join event_dates evtdt on (ven.id=evtdt.venue_id) where evtdt.venue_id is null or not ('2009-12-06 00:00:00' between evtdt.event_from_datetime                                   and evtdt.event_to_datetime);+----+---------------------+---------------------+| id | event_from_datetime | event_to_datetime   |+----+---------------------+---------------------+|  1 | 2009-12-09 00:00:00 | 2009-12-12 00:00:00 ||  2 | 2009-12-15 00:00:00 | 2009-12-20 00:00:00 ||  3 | NULL                | NULL                ||  5 | NULL                | NULL                |+----+---------------------+---------------------+

如果您注意到结果，它不包括日期在 2009-12-06 00:00:00 之间但显示其他预订的地点 ID 1。请帮助我更正此查询。

提前致谢。

Answer 1

SELECT  *
FROM    venue v
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    event_dates ed
        WHERE   ed.venue_id = v.id
                AND '2009-12-06 00:00:00' BETWEEN ed.event_from_datetime AND ed.event_to_datetime
        )