mysql GROUP_CONCAT duplicates

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

Answers


You need to use the DISTINCT option:

GROUP_CONCAT(DISTINCT animal)

Need Your Help

Don't know how to generate the query I need using Entity Framework 4

c# linq entity-framework-4

I've got an Entity Framework 4 model. There's 2 tables in that model, Subscribers and Versions:

Chrome extension: move downloaded file to input field

javascript google-chrome plugins

I am trying to migrate images with a small chrome extension i have built. the extension consists of 5 files:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.