Menu
Problem: I have a GROUP_CONCAT query that is working as intended, except I'd like to make the concat a joined answer, not the raw ID field.
Current query:
SELECT user.user_id, user.user, GROUP_CONCAT(user_roles.roleID separator ', ') roles
FROM user
JOIN user_roles ON user.user_ID = user_roles.user_ID
GROUP BY users.user_ID, users.user
Gives result:
+----------+---------+----------------------------+
| user_ID | user | roles |
+----------+---------+----------------------------+
| 1 | Smith | 1, 3 |
+----------+---------+----------------------------+
| 2 | Jones | 1, 2, 3 |
+----------+---------+----------------------------+
Desired result:
+----------+---------+----------------------------+
| user_ID | user | roles |
+----------+---------+----------------------------+
| 1 | Smith | Admin, Other |
+----------+---------+----------------------------+
| 2 | Jones | Admin, Staff, Other |
+----------+---------+----------------------------+
User table:
+----------+---------+
| user_ID | user |
+----------+---------+
| 1 | Smith |
+----------+---------+
| 2 | Jones |
+----------+---------+
*users_roles table:*
+----------+---------+
| user_ID | role_ID |
+----------+---------+
| 1 | 1 |
+----------+---------+
| 2 | 1 |
+----------+---------+
| 2 | 2 |
+----------+---------+
| 2 | 3 |
+----------+---------+
| 1 | 3 |
+----------+---------+
roles table:
+----------+-----------+
| role_ID | role_name |
+----------+-----------+
| 1 | Admin |
+----------+-----------+
| 2 | Staff |
+----------+-----------+
| 3 | Other |
+----------+-----------+
213
The difference here is while CONCAT is used to combine values across columns, GROUP_CONCAT gives you the capability to combine values across rows. It's also important to note that both GROUP_CONCAT and CONCAT can be combined to return desired results.
The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value. Otherwise, it returns NULL.
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
The SEPARATOR specifies a literal value inserted between values in the group. If you do not specify a separator, the GROUP_CONCAT function uses a comma (,) as the default separator. The GROUP_CONCAT function ignores NULL values. It returns NULL if there was no matching row found or all arguments are NULL values.
try the following query
SELECT user.user_id, user.user, GROUP_CONCAT(roles.role_name separator ', ') roles
FROM user
JOIN user_roles ON user.user_ID = user_roles.user_ID
JOIN roles ON user_roles.role_ID= user_roles.role_ID
GROUP BY users.user_ID, users.user
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
