Duplicate GROUP_CONCAT when using JOIN

Question

I'm creating a sort of geocache team building system and I'm having trouble with my query.

To collate and display the individuals against the teams I'm using GROUP_CONCAT, however when I try to include the locations (listed as markers) the individuals names are being duplicated based on the number of markers.

I have put together a SQL Fiddle to explain my workings.

Here is my query:

SELECT SQL_CALC_FOUND_ROWS
            map_team.id AS team_id,
            map_team.name,
            GROUP_CONCAT(firstname, ' ', surname SEPARATOR ', ') AS full_name
            FROM map_team
            LEFT JOIN members
                ON members.map_team=map_team.id
            WHERE members.map_team IS NOT NULL
            GROUP BY map_team.name
            ORDER BY map_team.name ASC

Any advice would be appreciated.

Answer 1

GROUP_CONCAT( distinct ... ) will not give correct answers as the team member names in a team can be same. If you want to get it in a single query you can use:

SELECT SQL_CALC_FOUND_ROWS
    map_team.id AS team_id,
    map_team.name,
    GROUP_CONCAT(firstname, ' ', surname SEPARATOR ', ') AS full_name,
    (select count(*) from map_markers where team_id = map_team.id) AS total_markers,
    (select count(*) from map_markers where team_id = map_team.id and status = 'completed') AS total_completed
    FROM map_team
    LEFT JOIN members
        ON members.map_team=map_team.id
    WHERE members.map_team IS NOT NULL
    GROUP BY map_team.name
    ORDER BY map_team.name ASC

If you don't like the idea of a subquery in select. You need to do it separately.