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.

John Woo · Accepted Answer · 2012-09-05 06:10:49Z

8

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

edited Sep 5, 2012 at 6:10

answered Sep 5, 2012 at 5:59

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Michael A Over a year ago

Do you know if there's an equivalent 'group_concat' function in SQL Server? I'm now wondering if I've always done this the long way!

John Woo Over a year ago

@Michael No, but you can simulate it. Click this LINK

John Woo Over a year ago

@Michael Here's SQLFiddle Demo for MSSQL for you. Hoe this helps you.

DarkKnightFan Over a year ago

@Michael glad you asked. I was also wondering how to do this in DBs like Oracle, DB2?

John Woo Over a year ago

@Gaurav Here's Oracle Version of GROUP_CONCAT

|

Adriaan Stander · Accepted Answer · 2012-09-05 05:59:04Z

0

Using GROUP_CONCAT(expr) you can try something like

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The default separator between values in a group is comma (“,”).

SELECT  t.TeamID Team,
        m.EmpName manager,
        GROUP_CONCAT(mem.EmpName) Members
FROM    Team t INNER JOIN
        Emp m   ON  t.ManagerID = m.EmpID INNER JOIN
        Emp mem ON  t.MemberID = mem.EmpID
GROUP BY    t.TeamID Team,
            m.EmpName

answered Sep 5, 2012 at 5:59

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Comments

juergen d · Accepted Answer · 2012-09-05 06:06:45Z

0

You need to join the EMP table twice and use group_concat to list the members seperately.

select TeamID as Team, 
       e2.EmpName as Manager,
       group_concat(e1.EmpName) as Members
from Team t
left outer join EMP e1 on t.MemberID = e1.EmpID
left outer join EMP e2 on t.ManagerID = e2.EmpID
group by TeamID

If you use a left outer join then you will get results even if there is no manager or members for a team. If you only want teams with members and manager you can use inner join.

edited Sep 5, 2012 at 6:06

answered Sep 5, 2012 at 6:00

juergen d

205k40 gold badges305 silver badges377 bronze badges

Comments

grant7bar7 · Accepted Answer · 2012-09-05 06:23:05Z

Define a view called vTeam with this definition:

SELECT dbo.Team.TeamID, dbo.EMP.EmpName AS Manager, EMP_1.EmpName AS Member, EMP_1.EmpID AS MemberID, dbo.EMP.EmpID AS ManagerID FROM
dbo.EMP AS EMP_1 RIGHT OUTER JOIN dbo.Team ON EMP_1.EmpID = dbo.Team.MemberID LEFT OUTER JOIN dbo.EMP ON dbo.Team.ManagerID = dbo.EMP.EmpID

This query will give you your result:

SELECT TeamID, Manager, MemberList = STUFF(( SELECT ',' + Member FROM vTeam as xx WHERE xx.TeamID = x.TeamID and x.Manager = xx.Manager FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') FROM dbo.vTeam AS x GROUP BY TeamID,Manager;

Team1 John Alex,Tom
Team2 Alex John,Alex,Tom

Collectives™ on Stack Overflow

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

4 Answers 4

SQLFiddle Demo

7 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related