php - 获取特定日期不在另一个表中的记录。 MySQL

Question

我需要取回某个日期不在另一个表中的数据。

+------+--------+---------------------+---------------------+
| calID| jobID  | startDate           | endDate             |
+-- ---+--------+---------------------+---------------------+
| 1    | 2      | 2016-05-13 00:00:00 | 2016-05-13 00:00:00 |              
| 2    | 3      | 2016-05-14 00:00:00 | 2016-05-14 00:00:00 |
| 3    | 1      | 2016-05-15 00:00:00 | 2016-05-15 00:00:00 |
+------+--------+---------------------+---------------------+

这是 job_calendar。我需要在给定日期与工作无关的员工。

+------+--------+---------+
| id   | calID  | staffID |
+-- ---+--------+---------+
| 1    | 1      | 1       |             
| 2    | 1      | 2       |
| 3    | 1      | 3       |
+------+--------+---------+

这是工作人员和职位的 job_staff 表。

+---------+-----------+----------+
| staffID | firstName | lastName |
+---------+-----------+----------+
| 1       | John      | Smith    |             
| 2       | Max       | Power    |
| 3       | Jane      | Doe      |
+---------+-----------+----------+

最后是resource_staff 表。这存储了所有用户信息。我之前一直在尝试一些查询，虽然我认为我已经很接近了，但我需要一些帮助。

SELECT
    *
FROM              
    resource_staff
LEFT JOIN 
    job_staff
ON 
    resource_staff.staffID = job_staff.staffID
LEFT JOIN
    job_calendar
ON
    job_staff.calID = job_calendar.calID
WHERE
    job_staff.staffID IS NULL
AND
    job_calendar.startDate = "2016-05-13 00:00:00"

任何帮助将不胜感激，抱歉，如果我造成了麻烦或其他方面。

编辑:

我希望获得用户选择的特定日期内尚未分配给作业的所有结果。

Answer 1

试试这个

SELECT
*
FROM              
resource_staff 
where 

staffID  NOT IN (select jc.staffID  from job_calendar as jc 
JOIN job_staff  as js ON js.calID=jc.calID 
WHERE "2016-05-13 00:00:00" BETWEEN jc.startDate and jc.endDate )