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
You need to use the DISTINCT
option:
GROUP_CONCAT(DISTINCT animal)