Search code examples
mysqlsqlgroup-bygroup-concat

Eliminate duplicate rows inside GROUP_CONCAT

I have duplicate records using this sql:

SELECT emp.id, first_name, second_name, last_name, department, positions,  
            GROUP_CONCAT(email, " ", email_type SEPARATOR " || ") AS mails,
            GROUP_CONCAT(number, " ", number_type SEPARATOR " || ") AS numbers
            FROM geography.employee AS emp
            INNER JOIN geography.employee_email AS ee ON emp.id = ee.email_FK
            LEFT JOIN geography.employee_number AS en ON emp.id = en.number_FK
            WHERE first_name = 'John' AND last_name = 'Ausini'
            GROUP BY emp.id

So I get these results for mails:

heyhey@abv.bg personal || heyhey@abv.bg personal || summer_geals@bwtc.co work || summer_geals@bwtc.co work || ivanov@abv.bg personal || ivanov@abv.bg personal

and for numbers:

+7654656656565 work || +7654656465655 personal || +7654656656565 work || +7654656465655 personal || +7654656656565 work || +7654656465655 personal

The strange thing is that in my second record the mails are not duplicating, mails:

hei@abv.bg personal || hurei@abv.bg personal || burei@abv.bg personal || work@bwtc.com work

...but in this record I do not have numbers and maybe this deffers it from the top one. My expected output is not repeating data per record as such(for first record example):

heyhey@abv.bg personal || summer_geals@bwtc.co work || ivanov@abv.bg personal

...and no duplicates for the numbers too. DISTINCT is not working too(if I put it after the SELECT), I tried it although I am not fan of masking up the problem.

My data in the tables:

enter image description here

enter image description here

enter image description here

Solution

  • Since you're only getting the information for a single user, I would go with Salman A's answer; but if you were going for multiple (or all) users, and users tend to have lots of emails and numbers, this version could be faster.

    SELECT emp.id, first_name, second_name, last_name, department, positions, ee.mails, en.numbers
FROM geography.employee AS emp
INNER JOIN (SELECT email_FK, GROUP_CONCAT(email, " ", email_type SEPARATOR " || ") AS mails 
            FROM geography.employee_email 
            GROUP BY email_FK
) AS ee ON emp.id = ee.email_FK
LEFT JOIN (SELECT number_FK, GROUP_CONCAT(number, " ", number_type SEPARATOR " || ") AS numbers 
           FROM geography.employee_number 
           GROUP BY number_FK
) AS en ON emp.id = en.number_FK
;