CODEWITHSUNDEEP
phpmysqldatabase
thursday_dan
thursday_dan

Reputation: 581

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.

Upvotes: 0

Views: 70

Answers (2)

mm2knet
mm2knet

Reputation: 76

try this:

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

just replace the date string with your date and it will answer a record that is not used at that date.

Upvotes: 0

JYoThI
JYoThI

Reputation: 12085

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 )

Upvotes: 1

Related Questions