mysql GROUP_CONCAT duplicates

Question

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 

Answer 1

You need to use the DISTINCT option:

GROUP_CONCAT(DISTINCT animal)