A column should contain multiple rows from another table

Question

id emp
1 jon
2 jane

id dept
1 hr
2 sales

emp_id dept_id
1 1
2 1
2 2

Result
Emp Dept
jon ___ hr
jane __ hr, sales

I have Employee table, Department table and a bridge table.

How do i write a query so that if an employee is in multiple departments, they are displayed in 1 column comma seperated?

Answer 1

You can join the three tables, aggregate by employee, and concatenate the departments of each employee with group_concat():

select e.emp, group_concat(d.dept) as depts
from emp e
inner join emp_dept ed on ed.emp_id = e.id
inner join dept d on d.id = ed.dept_id
group by e.id