Join one row to multiple rows in another table in Mysql

Question

I have a two tables called (user) and (department) one user can have an arbitrary number of department). Ex:

User

id     name
-----------
1     user1
2     user2
3     user3

Department

id     name
-----------
1     dept1
2     dept2
3     dept3

I have create another table call user_dept

user_dept

u_id     d_name
----------------
1           1
1           2
1           3
2           2
2           3

third table (user_dept) columns are FK from user table and department table.

what I want is to call users and there departments in single row separate between departments by (,).

Answer 1

You can use group_concat() and a correlated subquery:

select 
    u.*,
    (
        select group_concat(d.name)
        from user_departments ud 
        inner join departments d on d.d_id = ud.id
        where ud.u_id = u.id
    ) dept_names
from users u

This can also be done with a join, and outer aggregation:

select u.*, group_concat(d.name) dept_names
from users u
left join user_departments ud on ud.u_id = u.id
left join departments d on d.d_id = ud.id
group by u.id