Remove Duplicate Column Rows from Stored Procedure Result and concatenate other column

Question

I have this code for a stored procedure:

BEGIN
    SELECT teams.name,
            IFNULL(users.email,'N/A') as email,
            IFNULL(users.name,'N/A') as user_name,
            IFNULL(team_user.role,'N/A') as role
    FROM teams
        LEFT JOIN team_user ON teams.id=team_user.team_id
        LEFT JOIN users ON team_user.user_id=users.id
    WHERE role is NULL
    OR role='admin';
END

I would like to display the result of the stored procedure in a table. The following is an example of the table that it displays:

Team Name	User Name
Team1	AdminUser1ForTeam1
Team1	AdminUser2ForTeam1
Team 2	AdminUser1ForTeam1
Team3	AdminUser1ForTeam3
Team3	AdminUser2ForTeam3

The problem with this table is that if a team has more than 1 admin, the team name will be repeated in two separate rows. The following is the result I want to achieve:

Team Name	User Name
Team1	AdminUser1ForTeam1, AdminUser2ForTeam1
Team 2	AdminUser1ForTeam1
Team3	AdminUser1ForTeam3, AdminUser2ForTeam3

I tried using group_concat however that cannot detect when the team name is a duplicate. I can also use php to try and achieve the result. Any help is greatly appreciated!

score 1 · Accepted Answer · answered Jun 23 '21 at 15:45

Use aggregation. I suspect you really want:

SELECT t.name,
        COALESCE(GROUP_CONCAT(u.email), 'N/A') as email,
        COALESCE(GROUP_CONCAT(u.name), 'N/A') as user_name,
        COALESCE(GROUP_CONCAT(tu.role), 'N/A') as role
FROM teams t LEFT JOIN
     team_user tu
     ON t.id = tu.team_id AND tu.role = 'admin' LEFT JOIN
     users u
     ON tu.user_id = u.id
GROUP BY t.name

Remove Duplicate Column Rows from Stored Procedure Result and concatenate other column

1 Answers1