Join 2 children tables with a parent tables without duplicated

Posted by user1847866 on Stack Overflow See other posts from Stack Overflow or by user1847866
Published on 2012-11-23T16:18:35Z Indexed on 2012/11/23 17:05 UTC
Read the original article Hit count: 471

Filed under:
|
|
|

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 | [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 | [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         | [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         | [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                                      | [email protected]                                             |
| Amy      | NULL                                         | NULL                                                                |
+----------+----------------------------------------------+---------------------------------------------------------------------+

© Stack Overflow or respective owner

Related posts about php

Related posts about mysql