CODEWITHSUNDEEP
sqlgroup-concat
Joseph Gregory
Joseph Gregory

Reputation: 589

Duplicate GROUP_CONCAT when using JOIN

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.

Upvotes: 1

Views: 48

Answers (3)

DecoderReloaded
DecoderReloaded

Reputation: 514

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.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

use following query:

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

Upvotes: 1

juergen d
juergen d

Reputation: 204756

Use 

GROUP_CONCAT(distinct firstname ...

Upvotes: 1

Related Questions