Home > Enterprise >  MySQL joining with grouping did not join perfectly
MySQL joining with grouping did not join perfectly

Time:11-16

I have three table and want to join them but the join returns a single row multiple time when using group by parent id from contact table.

contact table-

id Name
1 Murad
2 Tajharul

phone table-

id contact_id phone
1 1 017
2 2 014
3 2 015

email table-

id contact_id email
1 1 [email protected]
2 1 [email protected]
3 1 [email protected]
4 2 [email protected]
5 2 [email protected]

Desired Output-

id Name Phone Email
1 Murad 017 [email protected],[email protected],[email protected]
2 Tajharul 014,015 [email protected],[email protected]

Here is what I have tried so far-

SELECT contact.name , GROUP_CONCAT(phone.phone) phone, GROUP_CONCAT(email.email) email
FROM 
    contact 
    JOIN phone ON contact.id = phone.contact_id
    JOIN email ON contact.id = email.contact_id
GROUP BY contact.id

MySQL Fiddle link: http://sqlfiddle.com/#!9/ded29f/1

CodePudding user response:

Use DISTINCT in group_concat

SELECT contact.name , GROUP_CONCAT(DISTINCT phone.phone) phone, GROUP_CONCAT(DISTINCT email.email) email
FROM 
    contact 
    JOIN phone ON contact.id = phone.contact_id
    JOIN email ON contact.id = email.contact_id
GROUP BY contact.id

CodePudding user response:

You can achieve this by using subquery and Distinct in Group concat:

SELECT T1.id, T1.name, T1.phone, GROUP_CONCAT(DISTINCT email.email) email
FROM 
    (SELECT contact.id, contact.name , GROUP_CONCAT(DISTINCT phone.phone) phone
    FROM 
        contact 
        JOIN phone ON contact.id = phone.contact_id
  GROUP BY contact.id) T1
JOIN email ON T1.id = email.contact_id
GROUP BY T1.id
  • Related