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 [email protected] 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 [email protected],[email protected]
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.
