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

`GROUP_CONCAT(UNIQUE(`animal`))` not working? – Linus Kleen Dec 30 '10 at 10:00 — Linus Kleen, Dec 30 '10 at 10:00

score 156 · Accepted Answer · answered Dec 30 '10 at 10:51

156

You need to use the DISTINCT option:

GROUP_CONCAT(DISTINCT animal)

answered Dec 30 '10 at 10:51

grahamparks

11

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? – Jam Ville Nov 19 '13 at 13:13
1

Gah, thank you. I tried DISTINCT, but didn't put it inside the GROUP_CONCAT, not sure what I expected that to do. Thanks. – Ryan Feb 14 '14 at 00:13
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? – XCS Nov 01 '20 at 22:31
@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? – grahamparks Nov 02 '20 at 20:05

1 Answers1