我需要在某个日期取回不在另一个表中的数据。
+------+--------+---------------------+---------------------+
| 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"
如有任何帮助，我们将不胜感激，如果我是个麻烦或其他方面的人，我会感到抱歉。
编辑：
我希望得到所有尚未分配给用户选择的特定日期的作业的结果。
尝试这个
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 )
试试这个：
SELECT
*
FROM
ressource_staff rs
LEFT JOIN (
SELECT
rs2.staffID
FROM
ressource_staff rs2
LEFT JOIN
job_staff js
ON
rs2.staffID=js.staffID
LEFT JOIN
job_calendar jc
ON
js.calID=jc.calID
WHERE
jc.startDate < "2016-05-13 00:00:00"
AND
jc.endDate > "2016-05-00:00:00") sq
ON
rs.staffID=sq.staffID
WHERE
sq.staffID IS NULL
只需将日期字符串替换为您的日期，它就会回答该日期未使用的记录。