0

I have a problem similar to Getting data from multiple tables into single row while concatenating some values but i am not able to understand it and i am a newbie is sql queries. I have few tables and i have to join them and get some concatenated data in a row. Description is as followings:

Table 1 - tasks(id,title,user_id)
id     title          user_id     tree_id
--     -----          -------     -------
1      test task         1          20

Table 2 - task_follower(id,user_id,task_id)
id     user_id   task_id
--     -------   -------
1        1          1

Table 3 - account_user(id,name,email)
id      name    email
--      ----    -----
1       rakesh  kumar3180@gmail.com

Table 2 - category(id,category)
id     category
--     ------- 
1        Project1

Table 2 - task_category(id,user_id,task_id)
id     task_id   category_id
--     -------   -------
1        1          1

And the query i run is:

    SELECT a.*, GROUP_CONCAT(b.name SEPARATOR ',') AS member_names, GROUP_CONCAT(b.email SEPARATOR ',') AS member_emails, GROUP_CONCAT(DISTINCT d.category) SEPARATOR ',') AS categories FROM tasks AS a INNER JOIN task_followers AS c ON a.id = c.task_id INNER JOIN account_user AS b ON c.user_id = b.id INNER JOIN task_category AS i ON a.id = i.task_id INNER JOIN category AS d ON i.category_id = d.id WHERE a.id = 1 AND a.user_id = 1 AND GROUP BY a.id

As the result of this query i get:

id    title       user_id   tree_id     member_names    member_emails
---   -----       -------   -------     -------------   -------------
1     test task      1        20        rakesh,rakesh   kumar3180@gmail.com,kumar3180@gmail.com

I am not able to understand why name and email is duplicated? Please help me to solve this problem and also if you could explain me where i went wrong conceptually?

I noticed that When the number of category associated with the task is more than one then this problem occurs. Please have a look at http://sqlfiddle.com/#!2/b96eb/1 I have created an example there to demonstrate my problem.

Community
  • 1
  • 1
Rakesh Kumar
  • 157
  • 1
  • 13

1 Answers1

1

you can remove the duplicate using DISTINCT

GROUP_CONCAT(DISTINCT b.name SEPARATOR ',')

the reason for having duplicate values is because it happens that one of the records from the one table has multiple matches on the other table. see here.

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • I got your point but there is a problem in this.. Lets say if two users have same name then it will not show both users.. I have edited the example [See Here](http://sqlfiddle.com/#!2/b75d3/1) – Rakesh Kumar Aug 19 '13 at 15:31
  • to make long story short, can you show your desired result based on your records above? – John Woo Aug 19 '13 at 15:38
  • Basically result is same as the fiddle output except these duplicate names and emails... I want all tasks, associated followers(comma separated) and categories(comma separated)... – Rakesh Kumar Aug 19 '13 at 16:05