6

I'm trying to get in a string two many-to-many associations. In this example, each team has an undetermined number of colours and an undetermined number won awards.

This is the schema:

enter image description here

And this is the query I'm using:

SELECT
    teams.name AS name,
    GROUP_CONCAT(colours.name) AS colours,
    GROUP_CONCAT(awards.name) AS awards
FROM
    teams

-- join colours
INNER JOIN teams_to_colours
        ON teams.id = teams_to_colours.team_id

INNER JOIN colours
        ON teams_to_colours.colour_id = colours.id

-- join awards
INNER JOIN teams_to_awards
        ON teams.id = teams_to_awards.team_id

INNER JOIN awards
        ON teams_to_awards.award_id = awards.id

WHERE
    teams.name="A-Team"

GROUP BY
    teams.id

The problem is that the colours and the awards get duplicated. Let's say A-Team has red and blue as colours, and as awards TrollAward and DarwinAward... the results I get from the SQL look like this:

name: "A-Team"
colours: "red,blue,red,blue"
awards: "TrollAward,DarwinAward,TrollAward,DarwinAward"

I have tried to join only one many-to-many, and works perfectly, so I guess I'm overseeing something with the multiple joins...

bgusach
  • 14,527
  • 14
  • 51
  • 68
  • 2
    See my answer here: [MySQL Group_Concat Repeating Values](http://stackoverflow.com/questions/11486266/mysql-group-concat-repeating-values/11486365#11486365) – ypercubeᵀᴹ Jun 20 '13 at 08:01

1 Answers1

4

The fast and dirty answer is to add DISTINCT in the GROUP_CONCAT() functions:

SELECT
    teams.name AS name,
    GROUP_CONCAT(DISTINCT colours.name) AS colours,
    GROUP_CONCAT(DISTINCT awards.name) AS awards
...

This may not be very efficient though with big tables. The second approach is to GROUP BY in two subqueries (derived tables), where you get the concatenation from awards in one and from colurs in the other, and then join these derived tables.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • Thank you very much. It works with the `DISTINCT`. Anyway, could you refer to any example where I can see how these derived tables work? – bgusach Jun 20 '13 at 08:14
  • 1
    See this [answer](http://stackoverflow.com/questions/14140288/mysql-aggregate-functions-in-query-with-two-joins-gives-unexpected-results/14140884#14140884) (where it says *"A third option ..."*) – ypercubeᵀᴹ Jun 20 '13 at 08:20