MySQL: How to use GROUP_CONCAT with WHERE comparison

Related searches

I have 2 tables: groups: a simple list of group names, and group_users: a list of groups and their users, e.g.:

group_id user_id
1        2
1        37
1        38
3        14
8        2
8        24
8        27

I have a GROUP_CONCAT query that is currently working:

SELECT g.group_id, g.groupname
,GROUP_CONCAT(DISTINCT CONVERT(gu.user_id, CHAR(2)) ORDER BY gu.user_id) AS group_members
FROM group_users gu, groups g
WHERE g.group_id = gu.group_id
GROUP BY gu.group_id
ORDER BY gu.group_id;

This produces a result:

group_id  groupname  group_members
1         Sales      2,37,38
3         Marketing  3
8         Production 2,24,27

My Problem: I'm trying to get a list of groups a specific user ID is a member of (e.g. here, user_id 2 should produce result rows 1 & 3).

However, when I change the criteria WHERE g.group_id = gu.group_id AND gu.user_id = 2, I get:

group_id  groupname  group_members
1         Sales      2
8         Production 2

The groups are filtered correctly, but the group_members list is limited to said ID.

I've seen many SO answers suggest using HAVING vs. WHERE, but that produces an error. I've tried JOINs, that doesn't work either.

How can I filter the groups_users table to only ones with user_id of 2, then grab the list of all members of those group_ids?

You can add a query to filter the desired groups:

SELECT g.group_id, g.groupname
,GROUP_CONCAT(DISTINCT CONVERT(gu.user_id, CHAR(2)) ORDER BY gu.user_id) AS group_members
FROM (SELECT group_id FROM group_users WHERE user_id=2) gu1
INNER JOIN group_users gu
ON gu.group_id=gu1.group_id
INNER JOIN groups g
ON g.group_id = gu.group_id
GROUP BY gu.group_id
ORDER BY gu.group_id;

MySQL GROUP_CONCAT Function By Practical Examples, This tutorial shows you how to use the MySQL GROUP_CONCAT function to concatenate strings from a group with various options. The MySQL GROUP_CONCAT () function is an aggregate function that concatenates strings from a group into a single string with various options. The following shows the syntax of the GROUP_CONCAT () function: GROUP_CONCAT (DISTINCT expression ORDER BY expression SEPARATOR sep);

You can just reference group_users twice, no subqueries needed:

SELECT g.group_id, g.groupname
   , GROUP_CONCAT(DISTINCT CONVERT(gu.user_id, CHAR(2)) ORDER BY gu.user_id) AS group_members
FROM group_users AS ug -- the specified User's groups
INNER JOIN groups AS g ON ug.group_id = g.group_id
INNER JOIN group_users AS gu ON g.group_id = gu.group_id
WHERE ug.user_id = 2
GROUP BY g.group_id
ORDER BY g.group_id
;

Also note that this, and every other answer so far, converted your implicit "comma" join query into one with explicit JOINs. This is because the comma join syntax has been considered archaic and hard to maintain for quite a while, and was pretty much replaced by explicit JOIN syntax over a decade ago for all but the simplest queries.

MySQL, Til now we have seen the use of GROUP_CONCAT() function to group the values of multiple rows that belongs to same column. But, using� 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.

The condition:

WHERE gu.user_id = 2

removes the rows before aggregation. Instead you can use a HAVING clause:

SELECT g.group_id, g.groupname
,GROUP_CONCAT(DISTINCT gu.user_id ORDER BY gu.user_id) AS group_members
FROM group_users gu INNER JOIN groups g
ON g.group_id = gu.group_id
GROUP BY gu.group_id
HAVING SUM(gu.user_id = 2) > 0
ORDER BY gu.group_id;

Also why do you convert user_ids to CHAR(2)? If a user_id is greater than 99 it will be truncated. Use:

DISTINCT gu.user_id

inside GROUP_CONCAT() and MySql will convert the integer value implicitly.

GROUP_CONCAT, Get a readable list of MariaDB users from the mysql.user table: The former example shows the difference between the GROUP_CONCAT 's ORDER BY� MySQL GROUP_CONCAT function. Tudip. 24 June 2016. Lets say there is a table named fictional_characters, that looks like: id id_novel character_name 1 1100 Harry

One option would be using a subquery with GROUP_CONCAT() grouped by group_id column containing a having clause to check out existence of user_id=2 :

SELECT g.group_id, g.groupname, gu.group_members
  FROM `groups` g
  JOIN (SELECT GROUP_CONCAT(DISTINCT user_id ORDER BY user_id) 
               AS group_members, group_id
          FROM `group_users` 
         GROUP BY group_id
        HAVING COUNT(CASE WHEN user_id = 2 THEN 1 END) > 0
        ) gu
    ON g.group_id = gu.group_id 
 ORDER BY gu.group_id;

+----------+------------+--------------+     
| group_id | groupname  | group_members| 
+----------+------------+--------------+
|  1       | Sales      | 2,37,38      |
|  8       | Production | 2,24,27      |
+----------+------------+--------------+

Use grave-accented groups as table name, because of being a preserved word.

Demo

Example : MySQL GROUP_CONCAT() function, MySQL GROUP_CONCAT() function returns a string with concatenated non- NULL value from a group. Also discussed example on MySQL� What is the best way to handle a join / group_concat query in MySQL. Ask Question Asked today. Active today. Viewed 2 times 0. I am looking for some feedback

How to quote values using MySQL group_concat?, can quote values using concat() and grop_concat() function from MySQL. The syntax is as follows −SELECT GROUP_CONCAT(CONCAT('� MySQL GROUP_CONCAT() function returns a string with concatenated non-NULL value from a group. Also discussed example on MySQL GROUP_CONCAT() function, GROUP_CONCAT() with order by and example, GROUP_CONCAT() with distinct, GROUP_CONCAT() with separator.

MySQL is the world's most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions to help you get MySQL up and running in a few easy steps. We also explain how to perform some basic operations with MySQL using the mysql client.

Beginning with MySQL 8.0.4, the server no longer attempts to infer context in this fashion. Instead, the function is executed using the arguments as provided, performing data type conversions to one or more of the arguments if and only if they are not all of the same type.

Comments
  • A sub-query to create a temp table, then adding 2 INNER JOINs. A perfect solution that I would not have thought of any time soon. Thanks!
  • Ah, the 2-character limitation... it's for a small company. I should adjust to 4 for optimism :) i need some sort of conversion, as the native GROUP_CONCAT result is a BLOB ('0X322...'), even with DISTINCT.