1

The tables below are an example table structure.

The query I am trying to write is something like:

SELECT * FROM jobs LEFT JOIN assigned ON jobs.id = assigned.job_id;

However, as you can see the left join will product multiple matches in the case of job 100, but the above query will only return one match.

Is it possible to concatenate the results into a comma separated string for the left join?

If possible, a step further would be, is it possible to replace the assigned.user_id with the users.name column in the concatenated comma separated string.

users

╔═══╦════════════╦═════════════╗
║   ║ id         ║ name        ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 1          ║ Matt        ║
║ 2 ║ 2          ║ Phil        ║
║ 3 ║ 3          ║ Chris       ║
╚═══╩════════════╩═════════════╝

jobs

╔═══╦════════════╦═════════════╗
║   ║ id         ║ name        ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 100        ║ Do this     ║
║ 2 ║ 101        ║ Do that     ║
║ 3 ║ 102        ║ And this    ║
╚═══╩════════════╩═════════════╝

assigned

╔═══╦════════════╦═════════════╗
║   ║ user_id    ║ job_id      ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 1          ║ 100         ║
║ 2 ║ 2          ║ 100         ║
║ 3 ║ 1          ║ 101         ║
╚═══╩════════════╩═════════════╝
Matt
  • 1,490
  • 2
  • 17
  • 41
  • possible duplicate of [Can I concatenate multiple MySQL rows into one field?](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) – Kavi Siegel Mar 05 '15 at 00:24

1 Answers1

2
SELECT jobs.id,
       jobs.name,
       GROUP_CONCAT( users.name order by users.name) as workersOnTheJob
   FROM 
      jobs 
         LEFT JOIN assigned 
            ON jobs.id = assigned.job_id
            LEFT JOIN users
               on assigned.user_id = users.id
   group by
      jobs.id,
      jobs.name
DRapp
  • 47,638
  • 12
  • 72
  • 142
  • This is working great when I add a where clause on the end for a single job, however without the where clause it only returns one result instead of all jobs. – Matt Mar 05 '15 at 00:33
  • Resolved by adding "GROUP BY jobs.id" at the end of the query --http://stackoverflow.com/questions/22190200/query-using-group-concat-is-returning-only-one-row – Matt Mar 05 '15 at 00:37