CODEWITHSUNDEEP
mysqlsqljoin
DarkKnightFan
DarkKnightFan

Reputation: 1953

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

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.

Upvotes: 5

Views: 3248

Answers (4)

grant7bar7
grant7bar7

Reputation: 118

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

Upvotes: -1

John Woo
John Woo

Reputation: 263893

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

Upvotes: 8

juergen d
juergen d

Reputation: 204904

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.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166576

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

Upvotes: 0

Related Questions