Retrieving data of vehicle drivers that have not been assigned to a job
I would start with the list of available drivers. As you appear to want them as a CSV string, it makes sense to use grouping and concatenate the names with GROUP_CONCAT(). Use an outer join of jobs to the cross-product of dates and drivers, then, to get the available drivers for each day, group-concatenate the driver name only if it has no matching job:
SELECT
listofdays.job_date,
GROUP_CONCAT(IF(j.job_id IS NULL, d.driver_name, NULL)) AS available_drivers
FROM
(
SELECT
'2016-09-19' + INTERVAL seq.seq DAY AS job_date
FROM
seq_0_to_999999 AS seq
WHERE
seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
) AS listofdays
CROSS JOIN t_drivers AS d
LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
AND d.driver_id = j.driver_id
GROUP BY
listofdays.job_date
;
The next, and last, step would be to use the above as a derived table and outer-join t_jobs
to it once more – this time to get the job details (and you would also need to outer-join t_drivers
and t_vehicle
to get details from those tables as well):
SELECT
sub.job_date,
j.job_id,
IFNULL(d.driver_name, '') as job_driver,
IFNULL(v.vehicle_reg, 'no') as job_vehicle,
j.collection_town,
j.collection_postcode,
j.delivery_town,
j.delivery_postcode,
j.job_status,
sub.available_drivers
FROM
(
SELECT
listofdays.job_date,
GROUP_CONCAT(IF(j.job_id IS NULL, d.driver_name, NULL)) AS available_drivers
FROM
(
SELECT
'2016-09-19' + INTERVAL seq.seq DAY AS job_date
FROM
seq_0_to_999999 AS seq
WHERE
seq.seq <= TIMESTAMPDIFF(DAY, '2016-09-19', '2016-09-25')
) AS listofdays
CROSS JOIN t_drivers AS d
LEFT JOIN t_jobs AS j ON listofdays.job_date = DATE(j.collection_datetime)
AND d.driver_id = j.driver_id
GROUP BY
listofdays.job_date
) AS sub
LEFT JOIN t_jobs AS j ON sub.job_date = DATE(j.collection_datetime)
LEFT JOIN t_drivers AS d ON j.driver_id = d.driver_id
LEFT JOIN t_vehicles AS v ON j.vehicle_id = v.vehicle_id
ORDER BY
job_date ASC,
job_order ASC
;