Need to concatenate results of a subquery in a select statement

Question

I have three tables

Table1: Users
Columns: User_ID (int), FirstName, LastName....
Values
1            Jane        Doe
2            John        Doe
3            Mike        Smith

Table2: User_Groups
Columns: User_ID (int), Group_ID(int)
Values:
Row1: 1          2  
Row2: 1          3
Row3: 2          1
Row4: 2          3
Row5: 3          1

Table3: Groups
Columns: Group_ID (int), GroupName(varchar)
Values
Row1: 1         Admin
Row2: 2         Power User
Row3: 3         Developer

I would like to create a query that can return the results in the following way: **RESULT

UserID GroupNames
Row1: 1      Power User, Developer
Row2: 2      Admin, Developer
Row3: 3      Admin

In SQL Server - I was able to achieve it using something like this:

SELECT User_ID,
  SUBSTRING( 
     replace( 
         replace( 
     (SELECT Groups.GroupName
  FROM User_Groups, Groups 
  where groups.Group_ID = 
                  User_Groups.Group_ID AND
                User_Groups.User_ID =Users.User_ID
          FOR XML PATH('') )
          ,'<GROUPNAME>',', ')
      ,'</GROUPNAME>',''),3,2000) as UserGroups  
FROM User_Groups LEFT JOIN Groups ON 
User_Groups.Group_ID=Groups.Group_ID
ORDER BY User_ID ASC

I wanted to do get a similar final result in MySQL (tried GROUP_CONCAT etc) but unsuccessful.. how can I get similar **RESULT in MySQL. Please note the tables exist already and I cant change them. Any help will be greatly appreciated

Answer 1

This works:

SELECT
    t1.User_ID AS UserID,
    (
        SELECT
            GROUP_CONCAT(t2.GroupName)
        FROM
            Groups t2
        WHERE
            t2.Group_ID IN(
                    SELECT
                        t3.Group_ID
                    FROM
                        User_Groups t3
                    WHERE
                        t3.iUser_ID = t1.User_ID
            )
    ) AS GroupNames
FROM
    Users t1

And this look like better idea, since you don't want to have user names, so that's no need to involve Users table:

SELECT
    User_ID,
    GROUP_CONCAT(GroupName) AS GroupNames
FROM
    (
        SELECT
            t2.User_ID AS User_ID,
            t3.GroupName AS GroupName
        FROM
            User_Groups t2
        LEFT JOIN
            Groups t3 ON (t3.Group_ID = t2.Group_ID)
    ) tmp
GROUP BY
    User_ID