Left Join duplicates

Question

I have several tables I need to query in order to get all the rows for a certain user. The tables basically look like this

contact
=======
id_contact PK
firstName
lastName
...

contact_phone
===============
id_contact_phone, PK
id_contact, FK
id_phone_type, FK
phone
...

phone_type
============
id_phone_type PK
phone_type
....

And there's a bunch of tables similar to those except they are for email, phone etc. I need to display all that information for a given contact, I'm using several LEFT JOIN but I'm unsure on how to output the information.

This is my query

SELECT contact.id_contact, contact.lastName, contact.firstName, contact_email.email, email_type.email_type, contact_phone.phone, phone_type.phone_type, contact_company.contact_title, company.company_name
FROM contact 
    LEFT JOIN contact_email
        ON contact.id_contact = contact_email.id_contact
    LEFT JOIN email_type 
        ON contact_email.id_email_type = email_type.id_email_type
    LEFT JOIN contact_phone
        ON contact.id_contact = contact_phone.id_contact
    LEFT JOIN phone_type
        ON contact_phone.id_phone_type = phone_type.id_phone_type
    LEFT JOIN contact_company
        ON contact.id_contact = contact_company.id_contact
    LEFT JOIN company
        ON contact_company.id_company = company.id_company
WHERE contact.id_contact = $cid

My problem is that if a certain contact has several phone numbers, emails etc. the query will obviously return more than 1 row so I'm not exactly sure how to display the information since most of the columns will be duplicates of each others. Here's an example of what that query might return

+===========================================================================================+  
| id_contact | lastName | firstName | email            | email_type |  phone   | phone_type |
+===========================================================================================+
| 1          | Doe      | John      | [email protected] | Work       | 555-1234 | Work       |
+------------+----------+-----------+------------------+------------+----------+------------+  
| 1          | Doe      | John      | [email protected] | Work       | 555-2222 | Mobile     | 
+-------------------------------------------------------------------+----------+------------+
| 1          | Doe      | John      | [email protected]   | Personal   | 555-1234 | Work       | 
+------------+----------+-----------+------------------+------------+----------+------------+  
| 1          | Doe      | John      | [email protected]   | Personal   | 555-2222 | Mobile     |
+-------------------------------------------------------------------+----------+------------+

How can I display the information in php without having redundant data and can my query be optimized?

Answer 1

mysql has a wonderful group_concat function. Combined with GROUP BYid_contact`, this will do what you want. For your example:

SELECT contact.id_contact, contact.lastName, contact.firstName,
    GROUP_CONCAT(CONCAT(contact_email.email, ' : ', email_type.email_type) SEPARATOR ', ') AS email,
    GROUP_CONCAT(CONCAT(contact_phone.phone, ' : ', phone_type.phone_type) SEPARATOR ', ') AS phone,
    contact_company.contact_title, company.company_name
FROM contact 
    LEFT JOIN contact_email
        ON contact.id_contact = contact_email.id_contact
    LEFT JOIN email_type 
        ON contact_email.id_email_type = email_type.id_email_type
    LEFT JOIN contact_phone
        ON contact.id_contact = contact_phone.id_contact
    LEFT JOIN phone_type
        ON contact_phone.id_phone_type = phone_type.id_phone_type
    LEFT JOIN contact_company
        ON contact.id_contact = contact_company.id_contact
    LEFT JOIN company
        ON contact_company.id_company = company.id_company
WHERE contact.id_contact = $cid

Note that I've never used GROUP_CONCAT around a normal CONCAT, but I see no reason why it wouldn't work.

Answer 2

If your email and phone types are consistent you might could flatten it to one row by returning the different types as projected columns, for example for the email types:

SELECT contact.id_contact, contact.lastName, contact.firstName,
  contact_company.contact_title, company.company_name, work_email.email AS work_email,
  personal_email.email as personal_email, mobile_phone.phone as mobile_phone,
  work_phone.phone as work_phone
FROM contact 
  LEFT JOIN contact_email AS work_email
    ON (contact.id_contact = work_email.id_contact AND 
      work_email.id_email_type = email_type.id_email_type AND
      email_type.email_type = 'Work')
  LEFT JOIN contact_email AS personal_email
    ON (contact.id_contact = personal_email.id_contact AND 
      personal_email.id_email_type = email_type.id_email_type AND
      email_type.email_type = 'Personal')
  LEFT JOIN contact_phone AS mobile_phone
    ON (contact.id_contact = mobile_phone.id_contact AND 
      mobile_phone.id_phone_type = phone_type.id_phone_type AND
      phone_type.phone_type = 'Mobile')
  LEFT JOIN contact_phone AS work_phone
    ON (contact.id_contact = work_phone.id_contact AND 
      work_phone.id_phone_type = phone_type.id_phone_type AND
      phone_type.phone_type = 'Work')
WHERE contact.id_contact = $cid

Answer 3

If you have an inconsistent/unknown number of emails or phone numbers, etc, then you might be better off retrieving the data via multiple select statements. It really all depends on how you want to use the data on the web server side. Odds are that you don't actually need it in this table format though.

If you're worried about the performance of running multiple queries, remember that you can sometimes put multiple statements in a single query and have multiple result sets returned. I don't know if this is something that you can do with PHP but I would assume it is.

Answer 4

Since the types are unknown you can use some grouping on the PHP side by expanding on another answer in a similar question on PHP: Grouping Records from While Loop

First be sure that you're ordering on the id_contact and types in the query:

...
WHERE contact.id_contact = $cid
ORDER BY contact.id_contact, email_type.email_type, phone_type.phone_type

Then do a manual grouping on the row displays while looping through the result rows:

$contact_id = 0;
while($row = mysql_fetch_array($result))
{
    if( $row['contact_id'] != $contact_id)
    {
        echo '<hr><br><h3>Contact: ' . $row['first_name'] . $row['last_name']  . '</h3>';
        $contact_id= $row['contact_id'];
        $phone_type = null;
        $email_type = null;
    }
    if ($row['email_type] != $email_type)
    {
       echo $row['email_type'] . ' Email: ' . $row['email'];
       $email_type = $row['email_type']
    }
    if ($row['phone_type] != $phone_type)
    {
       echo $row['phone_type'] . ' Phone: ' . $row['phone'];
       $phone_type = $row['phone_type']
    }
}