3 tables and 2 independent JOINs with CONCAT expressions

Question

I have the following 3 tables with IDs

Table: users

user | name
  1  | Joe
  2  | John

Table: user_id1

user | id1
  1  |   2
  1  |   3
  2  |   5

Table: user_id2

user | id2
  1  |   3
  1  |   4

I would like to get the following result for each user

name | ids 1 | ids 2
 Joe | 2,3   | 3,4
 John| 5     | NULL

I use this query:

SELECT
  user.name,
  GROUP_CONCAT(user_id1.id1) AS "ids1",
  GROUP_CONCAT(user_id2.id2) AS "ids2"

FROM users

LEFT JOIN user_id1
  ON user_id1.user=users.user

LEFT JOIN user_id2
  ON user_id2.user=users.user

But I get this result:

name | ids1    | ids2
Joe  | 2,2,3,3 | 3,4,3,4
John | 5       | NULL

What is wrong? Thanks for your help

Answer 1

Using DISTINCT should solve it

SELECT
  user.name,
  GROUP_CONCAT(DISTINCT user_id1.id1) AS "ids1",
  GROUP_CONCAT(DISTINCT user_id2.id2) AS "ids2"

FROM users

LEFT JOIN user_id1
  ON user_id1.user=users.user

LEFT JOIN user_id2
  ON user_id2.user=users.user