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)
| Team | Manager | Members |
| team1 | John | Alex, Tom |
I think you need to use
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 ),...