mysql GROUP_CONCAT duplicates

SqlMysqlGroup Concat

Sql Problem Overview


I make my join from a farmTOanimal table like this. There is a similar farmTotool table

id | FarmID  | animal
 1 |    1    | cat
 2 |    1    | dog

When I join my tables in a view, I get a result that looks like this

FarmID | animal | tool
   1   |  cat   | shovel
   1   |  dog   | shovel
   1   |  cat   | bucket
   1   |  dog   | bucket

Now, I do GROUP BY FarmID, and GROUP_CONCAT(animal) and GROUP_CONCAT(tool), i get

FarmID |     animals     |         tools
  1    | cat,dog,cat,dog | shovel,shovel,bucket,bucket

But, what I really want is a result that looks like this. How can I do it?

FarmID | animals |    tools
  1    | cat,dog | shovel,bucket

Sql Solutions


Solution 1 - Sql

You need to use the DISTINCT option:

GROUP_CONCAT(DISTINCT animal)

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionMattView Question on Stackoverflow
Solution 1 - SqlgrahamparksView Answer on Stackoverflow