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

Unforgettable

GROUP_CONCAT(UNIQUE(animal)) not working? – Herakleion

Answer 1

You need to use the DISTINCT option:

GROUP_CONCAT(DISTINCT animal)

Camilia

DISTINCT displays same rows content as one. For ex. row1='abc', row2='def', row3='abc'. The GROUP_CONCAT(DISTINCT row) will result abc,def which supposed to be abc,def,abc. What's is the solution other than using DISTINCT? – Underfoot

Gah, thank you. I tried DISTINCT, but didn't put it inside the GROUP_CONCAT, not sure what I expected that to do. Thanks. – Del

I agree with @JamVille, this solution only works if row column contents are indeed distinct. What if the contents are not distinct, how can we select the values only once? – Gable

@JamVille If you use GROUP_CONCAT() without the DISTINCT keyword that will do what you ask for in your example, unless I'm missing something? – Camilia

Answer 2

Also if you want to change the default separator , use this syntax :

GROUP_CONCAT(DISTINCT animal || '\n')

Rakish

Answer 3

GROUP_CONCAT(DISTINCT animals)

Grassland

Your answer is essentially a duplicate of the accepted answer. – Insphere