Hey guys, I’m working on a database for employee training. I’ve got this bridge table that shows which courses each employee needs to take and when they finish them. Here’s what my tables look like:
employee_courses: [worker_id^, lesson_id^, finish_date]
staff: [id^, given_name, family_name]
lessons: [lesson_id, lesson_title]
^foreign_key
My employee_courses table has data like this:
|worker_id | lesson_id | date
| 1 | 1 | null
| 1 | 2 | null
I’m using a loop to make a table, but it’s copying the worker_id for each lesson_id. How can I put all the lesson_ids together so I can group them by worker_id? I’m not sure how to combine these columns. Any ideas?
Hey Emma_Brave! That’s an interesting challenge you’ve got there with your employee training database. 
I’m curious, have you considered using a GROUP_CONCAT function or something similar? It might help you group those lesson_ids together by worker_id without repeating the worker_id for each lesson.
What database system are you using? Different systems have different ways to concatenate strings, so knowing that could help tailor the solution.
Also, I’m wondering about your end goal here. Are you trying to create a report that shows all courses an employee needs to take at a glance? Or is there another purpose for grouping these lesson_ids?
It’d be great to hear more about what you’re trying to achieve. Maybe there’s a creative way to structure your query or even adjust your table design to make this easier. What do you think?
hey emma, i’ve dealt with similar stuff. tried using a subquery with group_concat? something like: SELECT worker_id, GROUP_CONCAT(lesson_id) as lessons FROM employee_courses GROUP BY worker_id. hope that helps!
I’ve tackled a similar issue in my work with training databases. One approach that’s worked well for me is using a subquery with GROUP_CONCAT (or its equivalent, depending on your database system). This allows you to aggregate the lesson_ids for each worker.
Here’s a basic SQL query that might help:
SELECT e.worker_id, s.given_name, s.family_name,
GROUP_CONCAT(e.lesson_id ORDER BY e.lesson_id) AS lesson_ids
FROM employee_courses e
JOIN staff s ON e.worker_id = s.id
GROUP BY e.worker_id, s.given_name, s.family_name
This query will give you a row per worker with all their lesson_ids concatenated. You can then use this result in your loop to create the table you need. Remember to adjust the syntax based on your specific database system.
Hope this helps point you in the right direction. Let me know if you need any clarification or have additional questions.