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 use DISTINCT inside the GROUP_CONCAT like this:

GROUP_CONCAT(DISTINCT b.email SEPARATOR ',') AS member_emails
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • But this is not possible with name.. Because name can't be unique.. Actually i have to display comma separated names and if name is not available then email.. Is it possible? – Rakesh Kumar Aug 02 '13 at 12:27
  • @user2645698 name is not unique many can have same name . and in your code you got it dublicated which you should use distinct as mahmoud told you – echo_Me Aug 02 '13 at 12:30
  • @user2645698 - Sorry, but I can't understand you, can you please post some extra sample data explaining that issue, you can also update [**this demo**](http://sqlfiddle.com/#!2/dd1d2/1) showing this problem. – Mahmoud Gamal Aug 02 '13 at 12:32
  • I understand what you say.. Okay let me put it this way: Say there are two users User1 and User1 Now if i invite both of them and then display their names using DISTINCT, it won't work properly. So i have to address this issue also. – Rakesh Kumar Aug 02 '13 at 12:35
  • @user2645698 - Do you mean that the user can have multiple emails? and you need to select only one email? can you please update your question with a complete sample data explaining this issue with the desired output? this will be very helpful. Thanks. – Mahmoud Gamal Aug 02 '13 at 12:40
  • @user2645698 - Add `DISTINCT` see [this updated demo](http://sqlfiddle.com/#!2/19d87/4) – Mahmoud Gamal Aug 02 '13 at 14:12