title: Need to concatenate results of a subquery in a select statement
body: <p>I have three tables</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I would like to create a query that can return the results in the following way:
  **RESULT</p>
  
  <pre><code>UserID GroupNames
  Row1: 1      Power User, Developer
  Row2: 2      Admin, Developer
  Row3: 3      Admin
  </code></pre>
  
  <p>In SQL Server - I was able to achieve it using something like this:</p>
  
  <pre><code>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('') )
            ,'&lt;GROUPNAME&gt;',', ')
        ,'&lt;/GROUPNAME&gt;',''),3,2000) as UserGroups  
  FROM User_Groups LEFT JOIN Groups ON 
  User_Groups.Group_ID=Groups.Group_ID
  ORDER BY User_ID ASC
  </code></pre>
  
  <p>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</p>