结合具有类似ID的课程列

Question

I have a bridge table that helps me track the employees and the mandatory courses they have to take and keep track of when the do it.

completed_training [employee_id^, course_id^, date_completed]
employees [id^, first_name, last_name]
training_courses [course_id, course_name]

^foreign_key

data in the completed_training bridge

|employee_id | course_id | date
| 1          | 1         | null
| 1          | 2         | null

I have a while loop that goes through and builds out a table and duplicates the employee id with the course_id. How do I combine the course_id onto one so I can group the employee_id?

Answer 1

All right, here's my guess at what you want, and how you can get it:

|employee_id | course_ids |
| 1          | 1,2        |

To get this result, your select statement will be similar, but with a little extra. First, you will use a GROUP BY phrase to tell MySQL to bunch together results with the same employee_id. Then you will use a group function to tell the query how to combine the results for the ids column.

Presumably right now you have something like

SELECT * from completed_training

You might be joining that with other stuff as well, without your actual query I can't know that. But that query will yield your example output above.

You can modify that query to bunch the data together based on employee_id:

SELECT * from completed_training GROUP BY employee_id

That will give you one row per employee, but which course_id you get is undefined. But now that you are grouping the result, you can use one of the aggregate functions to combine the course ids together.

SELECT employee_id, GROUP_CONCAT(course_id SEPARATOR ',') from completed_training GROUP BY employee_id

That will create a result with the course_ids combined into a single column.