Join 2 children tables with a parent tables without duplicated
- by user1847866
Problem
I have 3 tables: People, Phones and Emails. Each person has an UNIQUE ID, and each person can have multiple numbers or multiple emails.
Simplified it looks like this:
+---------+----------+
| ID | Name |
+---------+----------+
| 5000003 | Amy |
| 5000004 | George |
| 5000005 | John |
| 5000008 | Steven |
| 8000009 | Ashley |
+---------+----------+
+---------+-----------------+
| ID | Number |
+---------+-----------------+
| 5000005 | 5551234 |
| 5000005 | 5154324 |
| 5000008 | 2487312 |
| 8000009 | 7134584 |
| 5000008 | 8451384 |
+---------+-----------------+
+---------+------------------------------+
| ID | Email |
+---------+------------------------------+
| 5000005 | [email protected] |
| 5000005 | [email protected] |
| 5000008 | [email protected] |
| 5000008 | [email protected] |
| 5000008 | [email protected] |
| 8000009 | Ashley[email protected] |
| 5000004 | [email protected] |
+---------+------------------------------+
I am trying to joining them together without duplicates. It works great, when I try to join only Emails with People or only Phones with People.
SELECT People.Name, People.ID, Phones.Number
FROM People
LEFT OUTER JOIN Phones ON People.ID=Phones.ID
ORDER BY Name, ID, Number;
+----------+---------+-----------------+
| Name | ID | Number |
+----------+---------+-----------------+
| Steven | 5000008 | 8451384 |
| Steven | 5000008 | 24887312 |
| John | 5000005 | 5551234 |
| John | 5000005 | 5154324 |
| George | 5000004 | NULL |
| Ashley | 8000009 | 7134584 |
| Amy | 5000003 | NULL |
+----------+---------+-----------------+
SELECT People.Name, People.ID, Emails.Email
FROM People
LEFT OUTER JOIN Emails ON People.ID=Emails.ID
ORDER BY Name, ID, Email;
+----------+---------+------------------------------+
| Name | ID | Email |
+----------+---------+------------------------------+
| Steven | 5000008 | [email protected] |
| Steven | 5000008 | [email protected] |
| Steven | 5000008 | [email protected] |
| John | 5000005 | [email protected] |
| John | 5000005 | [email protected] |
| George | 5000004 | [email protected] |
| Ashley | 8000009 | Ashley[email protected] |
| Amy | 5000003 | NULL |
+----------+---------+------------------------------+
However, when I try to join Emails and Phones on People - I get this:
SELECT People.Name, People.ID, Phones.Number, Emails.Email
FROM People
LEFT OUTER JOIN Phones ON People.ID = Phones.ID
LEFT OUTER JOIN Emails ON People.ID = Emails.ID
ORDER BY Name, ID, Number, Email;
+----------+---------+-----------------+------------------------------+
| Name | ID | Number | Email |
+----------+---------+-----------------+------------------------------+
| Steven | 5000008 | 8451384 | [email protected] |
| Steven | 5000008 | 8451384 | [email protected] |
| Steven | 5000008 | 8451384 | [email protected] |
| Steven | 5000008 | 24887312 | [email protected] |
| Steven | 5000008 | 24887312 | [email protected] |
| Steven | 5000008 | 24887312 | [email protected] |
| John | 5000005 | 5551234 | [email protected] |
| John | 5000005 | 5551234 | [email protected] |
| John | 5000005 | 5154324 | [email protected] |
| John | 5000005 | 5154324 | [email protected] |
| George | 5000004 | NULL | [email protected] |
| Ashley | 8000009 | 7134584 | Ashley[email protected] |
| Amy | 5000003 | NULL | NULL |
+----------+---------+-----------------+------------------------------+
What happens is - if a Person has 2 numbers, all his emails are shown twice (They can not be sorted! which means they can not be removed by @last)
What I want:
Bottom line, playing with the @last, I want to end up with somethig like this, but @last won't work if I don't arrange ORDER columns in the righ way - and this seems like a big problem..Orderin the email column. Because seen from the example above:
Steven has 2 phone number and 3 emails. The JOIN Emails with Numbers happens with each email - thus duplicated values that can not be sorted (SORT BY does not work on them).
**THIS IS WHAT I WANT**
+----------+---------+-----------------+------------------------------+
| Name | ID | Number | Email |
+----------+---------+-----------------+------------------------------+
| Steven | 5000008 | 8451384 | [email protected] |
| | | 24887312 | [email protected] |
| | | | [email protected] |
| John | 5000005 | 5551234 | [email protected] |
| | | 5154324 | [email protected] |
| George | 5000004 | NULL | [email protected] |
| Ashley | 8000009 | 7134584 | Ashley[email protected] |
| Amy | 5000003 | NULL | NULL |
+----------+---------+-----------------+------------------------------+
Now I'm told that it's best to keep emails and number in separated tables because one can have many emails. So if it's such a common thing to do, what isn't there a simple solution?
I'd be happy with a PHP Solution aswell.
What I know how to do by now that satisfies it, but is not as pretty.
If I do it with GROUP_CONTACT I geat a satisfactory result, but it doesn't look as pretty: I can't put a "Email type = work" next to it.
SELECT People.Ime,
GROUP_CONCAT(DISTINCT Phones.Number),
GROUP_CONCAT(DISTINCT Emails.Email)
FROM People
LEFT OUTER JOIN Phones ON People.ID=Phones.ID
LEFT OUTER JOIN Emails ON People.ID=Emails.ID
GROUP BY Name;
+----------+----------------------------------------------+---------------------------------------------------------------------+
| Name | GROUP_CONCAT(DISTINCT Phones.Number) | GROUP_CONCAT(DISTINCT Emails.Email) |
+----------+----------------------------------------------+---------------------------------------------------------------------+
| Steven | 8451384,24887312 | [email protected],[email protected],[email protected] |
| John | 5551234,5154324 | [email protected],[email protected] |
| George | NULL | [email protected] |
| Ashley | 7134584 | Ashley[email protected] |
| Amy | NULL | NULL |
+----------+----------------------------------------------+---------------------------------------------------------------------+
