MySQL query: Join tables and display records as comma separated string in a single row

Question

Suppose I have the following two tables in my MySQL database:

Table 1:: EMP: EmpID, EmpName
eg. (1, 'John'), (2,'Alex'),(3,'Tom')

Table 2:: Team: TeamID, ManagerID, MemberID
eg. record1: (Team1, 1, 2), record2: (Team1, 1, 3) 

so there is a team with id team1, John is the manager and Alex and Tom are its members.

I want to display the records of the Team table on the screen in the following manner

| Team  | Manager | Members   |
| team1 | John    | Alex, Tom |

What should be SQL query which will join the above two tables and return me the names of the members when based on the memberIDs.

Also the result will be displayed as 1 row containing all the team members separated by a comma.

If there is a better way of designing these two tables then please suggest that also. It will be much appreciated.

Thanks.

Answer 1

I think you need to use GROUP_CONCAT. GROUP_CONCAT() function is used to concatenate column values into a single string. It is very useful if you would otherwise perform a lookup of many row and then concatenate them on the client end.

SELECT  b.TeamID as TeamName, 
        a.EmpName as Manager,
        GROUP_CONCAT(c.EmpName) Members
FROM    Emp a
            INNER JOIN Team b
                ON a.EmpID = b.ManagerID
            INNER JOIN  Emp c
                ON b.MemberID = c.EmpID
GROUP BY b.TeamID, a.EmpName

You can also change the separator, and the ordering

GROUP_CONCAT( c.EmpName SEPARATOR '-' ),...
GROUP_CONCAT( c.EmpName ORDER BY c.EmpName DESC ),...

SQLFiddle Demo