Avoid Duplicate in Mysql Left Join Query

Question

i have to take data from three tables like Userdetails,tasks,timedetails so i am getting data but it is duplicating based on the timedetails table. for example person id is - 1. it is presented two times in timedetails table then i'm getting duplicate rows. my query is

SELECT  GROUP_CONCAT(B.task_status) as task_status,
        GROUP_CONCAT(B.task_type) as task_type,
        GROUP_CONCAT(B.task_id) as task_id,
        GROUP_CONCAT(B.task_name) as task_name,
        A.us_id,
        A.us_name,
        C.out_time
FROM    ts_userdetails A
LEFT JOIN
        cms_task B
ON      B.emp_id = A.us_id
LEFT JOIN
        ts_timedetails C
ON      C.user_id=A.us_id
WHERE   C.entry_date='2017-05-09' AND
        A.us_id!='1'
GROUP BY C.user_id

I am getting results like

I don't want duplicated things in displayed fields.

If I have 2 timedetails for one particular person id means 2 times duplication occurs. I just want one time.

Please add your input, your result and the result you'd want as well formatted text. That image is barely readable without a magnifier... — Stefano Zanini
– Stefano Zanini, Commented May 9, 2017 at 8:16

Alexey · Accepted Answer · 2017-05-09 07:44:14Z

1

use distinct keyword

    SELECT GROUP_CONCAT(distinct B.task_status) as task_status,GROUP_CONCAT(distinct B.task_type) as task_type,GROUP_CONCAT(distinct B.task_id) as task_id,GROUP_CONCAT(distinct B.task_name) as task_name,A.us_id,A.us_name,C.out_time FROM ts_userdetails A LEFT JOIN cms_task B ON B.emp_id = A.us_id LEFT JOIN ts_timedetails C ON C.user_id=A.us_id WHERE C.entry_date='2017-05-09' AND A.us_id!='1' GROUP BY C.user_id

answered May 9, 2017 at 7:44

Alexey

2,4881 gold badge21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Karthick Anbazhagan Over a year ago

if i put distinct before group concat then status i will not get for all tasks. status are fixed 0,1,2

Alexey Over a year ago

If you look into the query you will notice that distinct is placed inside group_concat meaning it will affect only it's contents...

Alexey Over a year ago

Ah, I think I get you...well, that's not possible. you either have distinct values in group_concat or get all the duplicates.

Collectives™ on Stack Overflow

Avoid Duplicate in Mysql Left Join Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related