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 | |
---|---|---|
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 | |
---|---|---|---|
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