DISTINCT on INNER JOIN table

April 3, 2023

I’m trying to cast the not distinct table as one so the id table is distinct

I wrote a query to get roles from users but I want the emails to be distinct.

SELECT DISTINCT (users.id), (roles.name)
FROM users_roles 
INNER JOIN users ON users.id = users_roles.user_id
INNER JOIN roles ON roles.id = users_roles.role_id

The data I get back looks like this

+---------+--------------+
| ID      | Name         | 
+---------+--------------+
| 1       | Student      | 
| 2       | Admin        | 
| 3       | LBC          | 
| 2       | LBC          | 
| 4       | Examinator   |
| 5       | PO           | 
+---------+--------------+

The data I was hoping to get back looks like this so I would like to cast the names table as one if this is even possible

+---------+--------------+
| ID      | Name         | 
+---------+--------------+
| 1       | Student      | 
| 2       | Admin, LBC   | 
| 3       | LBC          | 
| 4       | Examinator   |
| 5       | PO           | 
+---------+--------------+

I’m using MySQL for my database

>Solution :

If you are using Oracle, you can use LISTAGG for this:

select id, LISTAGG(name, ',')
  from (
        SELECT DISTINCT users.id, roles.name
        FROM users_roles 
        INNER JOIN users ON users.id = users_roles.user_id
        INNER JOIN roles ON roles.id = users_roles.role_id
    )
 group by id

Here for unique ids names will be concatenated by comma.

Demo can be seen here.

If you use another dbms, solution would be similar but with other function (like group_concat for MySQL or string_agg for PostgreSQL or BigQuery). Most of them listed in this answer.

EDIT, For MySQL:

 select b.id, group_concat(b.name ORDER BY b.name SEPARATOR ',') AS g
  from (
        SELECT DISTINCT users.id, roles.name
        FROM users_roles 
        INNER JOIN users ON users.id = users_roles.user_id
        INNER JOIN roles ON roles.id = users_roles.role_id ) b
group by id

Demo for MySQL here.