MySQL: How to use GROUP_CONCAT with WHERE comparison
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
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
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.
WHERE gu.user_id = 2
removes the rows before aggregation.
Instead you can use a
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_id is greater than 99 it will be truncated.
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
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 | +----------+------------+--------------+
groups as table name, because of being a preserved word.
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.
- 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_CONCATresult is a BLOB ('0X322...'), even with