Daniel Sherwood Daniel Sherwood - 6 months ago 8
SQL Question

Get records which are not in another table for a certain date. MySQL

I need to get data back which isn't in another table, for a certain date.

+------+--------+---------------------+---------------------+
| 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 |
+------+--------+---------------------+---------------------+


This is the job_calendar. I need staff which are not associated with a Job for a given date.

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


This is the job_staff table for the staff member and the jobs.

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


And finally the resource_staff table. This stores all user information. I have been playing around with some queries before and although I think I'm close I need a little help.

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"


Any help would be greatly appreciated, sorry if I've been a nuisance or otherwise.

EDIT:

I expect to get all result which have not been assigned to a job for a particular date which the user selects.

Answer

try this

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 )
Comments