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?
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.