Join 2 children tables with a parent tables without duplicated

Question

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

Answer 1

What you want isn't actually what you want at all, if that makes any sense... you can't realistically do anything programatically with your database output, you need to do something with it (unless you're just running a query directly on your database).

And since you stated "I'd be happy with a PHP Solution aswell." ... what you really want is something like a PHP "User" object, something like (this is all hypothetical of course):

<?php

class User {

  private $_id;
  private $_telNos = array();
  private $_emails = array();

  public function __construct($iUserId = null, $oDatabaseAbstractionObject = null) {
    if(!is_null($iUserId)) $this->setId($iUserId);
    if(!is_null($oDatabaseConnectionObject)) $this->load($iUserId, $oDatabaseAbstractionObject);
  }

  public setId($iUserId) {
    $this->_id = (int) $iUserId;
  }

  public getId() {
    return $this->_id;
  }

  /* telephone and email setters and getters */

  public function load($iUserId, $oDatabaseAbstractionObject) {
    /* error trapping - for example if $iUserId is null */

    $this->setTelNos($oDatabaseAbstractionObject->readTelNos($iUserId));
    $this->setEmails($oDatabaseAbstractionObject->readEmails(iUserId));
  }
}

?>

Your database abstraction object then just needs to do some very simple queries to read your user, user email and phone number tables and return the results as arrays which you can then put straight into your PHP object. For example:

<?php

/**
 * this implements a database connection object as a private class member
 */
class DBUser {

  private $_conn;

  /* constructor other functionality */

  /**
   * method to pass an SQL query to the database and return an array of results
   */
  public function readTelNos($iUserId) {
    return $this->_conn->read("SELECT `number` from `tel` WHERE `user_id` = " . (int) $iUserId);
  }

}
?>

This breaks your problem down into smaller, easier to handle problems, and wraps them all up in nice PHP objects that you can then actually work with.

Your user object will have a list if phone numbers and emails that can easily be retrieved with something like $oUser->getEmails(); and if they're stored as an associative array inside the object you could even retrieve them by "label" $oUser->getEmail('work');