GROUP_CONCAT with multiple joins - what am I missing?

Question

I realise this is quite a common issue so I've spent a lot of time going through the various answers but non of them helped solve my problem.

I have a job request system in PHP / MySQL and I'm having issues with GROUP_CONCAT.

Here's my schema:

job
job_id
job_title

worker
worker_id
worker_name

workermap
workermap_id
job_id
worker_id

A table containing the jobs, a worker table for the possible workers and one to map workers to jobs. A job can have one or more workers, a worker can have one or more jobs.

When a job is logged it creates an entry in job but nothing in workermap until someone specifically assigns a worker. This means that when I query the jobs I do it with a left join as there are not entries in workermap for every job:

SELECT 
job.job_id,
job.job_title,
workermap.worker_id,
worker.worker_name

FROM job

LEFT JOIN workermap
ON job.job_id = workermap.job_id

LEFT JOIN worker
ON workermap.worker_id = worker.worker_id

The problem is this returns multiple rows if there are multiple workers assigned to a job:

+--------+-----------------------------+-------------+
| job_id | job_title                   | worker_name |
+--------+-----------------------------+-------------+
|    1   | New homepage                | Alan        |
|    1   | New homepage                | John        |
|    2   | Redesign footer             | John        |
|    2   | Redesign footer             | Sarah       |
|    3   | Update contact page content | NULL        |
+--------+-----------------------------+-------------+

What I want is this:

+--------+-----------------------------+-------------+
| job_id | job_title                   | worker_name |
+--------+-----------------------------+-------------+
|    1   | New homepage                | Alan, John  |
|    2   | Redesign footer             | John, Sarah |
|    3   | Update contact page content | NULL        |
+--------+-----------------------------+-------------+

I understand I need to use CONCAT to join the worker names together, but if I do this:

SELECT 
job.job_id,
job.job_title,
GROUP_CONCAT(worker.worker_name)

FROM job

LEFT JOIN workermap
ON job.job_id = workermap.job_id

LEFT JOIN worker
ON workermap.worker_id = worker.worker_id

Then I get just the one row with all the workers concatenated:

+--------+--------------+----------------------+
| job_id | job_title    | worker_name          |
+--------+--------------+----------------------+
|    1   | New homepage | Alan,John,John,Sarah |
+--------+--------------+----------------------+

I've tried the following:

SELECT 
job.job_id,
job.job_title,
workermap.worker_id,
workermap.worker_list

FROM job

LEFT JOIN 
    (
        SELECT workermap.worker_id, GROUP_CONCAT(worker_name) AS worker_list
        FROM workermap
        INNER JOIN worker
        ON workermap.worker_id = worker.worker_id
    )
AS workermap

SELECT 
job.job_id,
job.job_title,
(SELECT GROUP_CONCAT(worker.worker_name) 
FROM worker
WHERE worker.worker_id = workermap.worker_id)
AS combinedworkers

FROM job

LEFT JOIN workermap
ON job.job_id = workermap.job_id

SELECT 
job.job_id,
job.job_title,
workermap.worker_id,
worker.worker_list

FROM job

LEFT JOIN workermap
ON job.job_id = workermap.job_id

LEFT JOIN 
    (
        SELECT worker_id, GROUP_CONCAT(worker_name) AS worker_list
        FROM worker
        GROUP BY worker_id
    )
AS worker
ON workermap.worker_id = worker.worker_id

None of which got me any closer. I'm sure it's relatively simple but I can't work it out. Thanks!

Answer 1

This less complicated than you think. You can simply use aggregation on (job_id, job_title) and GROUP_CONCAT():

SELECT 
    j.job_id,
    j.job_title,
    GROUP_CONCAT(w.worker_name ORDER BY w.worker_name) worker_names
FROM job j
LEFT JOIN workermap wm ON job.job_id = wm.job_id
LEFT JOIN worker w ON wm.worker_id = w.worker_id
GROUP BY
    j.job_id,
    j.job_title