Mysql Question posted in
The official MySQL documentation can be found here.

MySQL join + GROUP_CONCAT

I have two MySQL tables and want to join it correctly by one sql query.

Table "users"

id email
1 [email protected]
2 [email protected]
3 [email protected]
4 [email protected]
5 [email protected]

Table "group_user"

user_id group_id
1 1
2 1
2 5
2 6
3 5
3 6
4 1
5 5

I want to have result:

id email group_ids
1 [email protected] 1
2 [email protected] 1,5,6
3 [email protected] 5,6
4 [email protected] 1
5 [email protected] 5

I don’t understand how to do it correctly. Please help me write the correct query to MySQL.

Tags:

2

Answers


  1. 0

    This can be done using the aggregate function GROUP_CONCAT() :

    SELECT user_id, email, GROUP_CONCAT(group_id) AS group_ids
FROM users u
INNER JOIN group_user g ON u.id = g.user_id
GROUP BY user_id, email
    Login or Signup to reply.
  2. 0

    with postgres you can use this request:

    SELECT u.id, u.email, array_agg(gu.group_id)
FROM users u LEFT JOIN group_user gu
ON u.id = gu.user_id
GROUP BY u.id, u.email;

    source: https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search