MySQL - need to concatenate results of subquery in a select statement

[PavanS]

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:
1 2
1 3
2 1
2 3
3 1

Table3: Groups
Columns: Group_ID (int), GroupName(varch ar)
Values:
1 Admin
2 Power User
3 Developer

I would like to create a query that can return the results in the following way:
**RESULT
UserID GroupNames
1 Power User, Developer
2 Admin, Developer
3 Admin

In SQL Server - I was able to achieve it using something like this:
SELECT User_ID,
SUBSTRING(
replace(
replace(
(SELECT Groups.GroupNam e
FROM User_Groups, Groups
where groups.Group_ID =
User_Groups.Gro up_ID AND
User_Groups.Use r_ID =Users.User_ID
FOR XML PATH('') )
,'<GROUPNAME>', ', ')
,'</GROUPNAME>','') ,3,2000) as UserGroups
FROM User_Groups LEFT JOIN Groups ON
User_Groups.Gro up_ID=Groups.Gr oup_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

[JKing]

Hi, GROUP_CONCAT is the way to go. It is important to remember to use the GROUP BY clause when you use an aggregate function such as GROUP_CONCAT

SELECT users.user_id, firstname, GROUP_CONCAT( groups.group_name )
FROM users
JOIN (
user_groups, groups
) ON ( users.user_id = user_groups.user_id
AND user_groups.group_id = groups.group_id )
GROUP BY users.user_id, firstname
LIMIT 0 , 30