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      | john.doe@123.com | Work       | 555-1234 | Work       |
+------------+----------+-----------+------------------+------------+----------+------------+  
| 1          | Doe      | John      | john.doe@123.com | Work       | 555-2222 | Mobile     | 
+-------------------------------------------------------------------+----------+------------+
| 1          | Doe      | John      | jdoe@email.com   | Personal   | 555-1234 | Work       | 
+------------+----------+-----------+------------------+------------+----------+------------+  
| 1          | Doe      | John      | jdoe@email.com   | Personal   | 555-2222 | Mobile     |
+-------------------------------------------------------------------+----------+------------+

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

I'm not sure what you're asking here. Do you want the first columns (id, lastName and firstName) to be NULL? Why doesn't that result set work for you? – it should be tribial to build a table with empty cells for "repeated data" with that result set, — Artefacto, Jun 28 '10 at 02:33
I want to display the information on the contact's profile page using `mysql_fetch_array` but right now that would either only display the first row (which will be missing results) or if I use a while loop I won't be able to tell if the emails are duplicates. I've updated my example — Serge, Jun 28 '10 at 03:12
It's pretty easy to tell if they are duplicates when you are using a loop: just order them with an ORDER BY clause and then include something like `if ($prev_value != row['email']) { $prev_value = row['email']; ... }` and insert the other processing code in the ... portion. If you're wanting to do that with each field, then it's still pretty easy to do something similar with an array of previous values, and just handle each one separately. — Jeffrey Blake, Jun 28 '10 at 03:56
@JGB146 performance wise I don't think that's a good idea, especially when listing all of the contacts... — Serge, Jun 28 '10 at 04:37
IMO, so many JOINS are a rather bad table design (I know, sometimes it IS necessary) but in this case, the `contact_company`, `contact_phone` and `phone_type` seem rather complicated for what they are supposed to do. A single table would fit way better without losing information or other drawbacks. — DrColossos, Jun 28 '10 at 05:40
I didn't see @JGB146's comment before I submitted my second answer below, but he's suggesting about the same thing and I think performance-wise it will be better than doing multiple queries in a loop because of the n+1 problem and multiple trips to the DB server. — Turnkey, Jun 28 '10 at 13:18
@DrColossos, I don't see how a single table would work for me since `contact` has a 1-to-many relationship with `contact_phone`, `contact_email`, etc. Unless I misunderstood what you meant... — Serge, Jun 28 '10 at 13:52
@Serge, You're correct if it's a requirement to keep the types and number of types as a variable. I think @DrColossos meant the more common case where there is a predetermined number of email/phone types. Only other way would be to hardwire some columns in the table, e.g. phone1, phone_type1, phone2, phone_type2, etc, but that would limit you on the number and is wasteful when the slots are not all used. You're data design is OK for generality, that's why I suggested the change be in the view layer if you really need that generality. — Turnkey, Jun 28 '10 at 15:36
Actually I ment moving all data together in a single `contact` table so you do not need to have a predefined set of colums like @Turnkey pointed out. So `contact` may have a row for each work, private and company all associated via 1:m to the contact (aka a person) — DrColossos, Jun 28 '10 at 16:51
@DrColossos - that would work if they carry the contact_type as a column in contact to eliminate the other join and then simply inner join contact to the contact_person table (as long as it can't be 0:m). — Turnkey, Jun 28 '10 at 17:56
@Turnkey: Yep, I think that would be my first approach without knowing exactly what goes on in other parts of the programm ;). — DrColossos, Jun 28 '10 at 18:20

score 0 · Answer 1 · answered Jun 28 '10 at 02:34

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.

I don't think this is a good idea. If he wants to build a table, it's easier to associate a certain e-mail with a certain contact type if they're on the same row, as opposed to in the same row and in the same position, separated by commas. — Artefacto, Jun 28 '10 at 02:37
I should have mentioned that I tried that before and it ends up giving duplicate entries. If the contact has 2 emails and 2 phone numbers the query will return 4 rows (2 with `john.doe@123.com` and 2 with `jdoe@email.com`). Also a contact can have more than the same type of email, phone etc. — Serge, Jun 28 '10 at 02:53

score 0 · Answer 2 · answered Jun 28 '10 at 04:21

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

that's the thing, they won't be consistent, the type could be changed or new ones might be added, granted there won't a hundred different types but I don't think I could hard code it — Serge, Jun 28 '10 at 04:39
In that case you will probably need to handle it on your display side in PHP, will add another post on how you might do that. — Turnkey, Jun 28 '10 at 12:38

score 0 · Answer 3 · answered Jun 28 '10 at 04:31

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.

score 0 · Answer 4 · edited May 23 '17 at 12:04

0

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']
    }
}

edited May 23 '17 at 12:04

Community

1
1

answered Jun 28 '10 at 12:59

Turnkey

9,266
3
27
36

I was hoping I'd be able to achieve this in a different way. I'll do a few tests and see what I get performance wise. The system is designed to only be used internally so for now there will never be more than 20 users logged on at the same time, I guess it shouldn't really degrade performance – Serge Jun 28 '10 at 16:41
Good idea to do some benchmarking, I think the performance impact will be negligible, should be able to render many hundreds of contacts in a fraction of a second. – Turnkey Jun 28 '10 at 18:04
Didn't have time to try till now and the query actually returns the same thing as in my example which means that it will still display duplicate entries for the phones.... – Serge Jun 28 '10 at 23:12
Yes, I see what you mean, the sorting gets thrown off in the phones since the first level of sort is in the email. Probably need to rethink the query so that the emails aren't correlated to the phones in the same query. Short of changing the data model probably need to union two queries together where the email part is null and vice-versa. – Turnkey Jun 29 '10 at 02:02

Left Join duplicates

4 Answers4