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(varchar)
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.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