左连接重复项

Question

我需要查询几个表才能获取某个用户的所有行。 这些表格基本上看起来像这样

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

，还有一堆与这些类似的表格，除了它们用于电子邮件、电话等。我需要显示给定联系人的所有信息，我正在使用多个 LEFT JOIN ，但我不确定如何输出信息。

这是我的查询

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

我的问题是，如果某个联系人有多个电话号码、电子邮件等，查询显然会返回超过 1 行，所以我不确定如何显示信息，因为大多数列都是重复的彼此。以下是该查询可能返回的示例

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

如何在 php 中显示信息而无需冗余数据以及我的查询是否可以优化？

Answer 1

mysql 有一个很棒的group_concat 函数。与 GROUP BYid_contact` 结合使用，这将满足您的需求。对于您的示例：

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

请注意，我从未在普通 CONCAT 周围使用过 GROUP_CONCAT，但我认为没有理由它不起作用。

Answer 2

如果您的电子邮件和电话类型一致，您可以通过将不同类型返回为投影列来将其展平为一行，例如对于电子邮件类型：

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

如果您的电子邮件或电话号码等数量不一致/未知，那么您最好通过多个选择语句检索数据。这实际上完全取决于您想要如何在 Web 服务器端使用数据。但很可能您实际上并不需要这种表格格式。

如果您担心运行多个查询的性能，请记住有时可以将多个语句放入单个查询中并返回多个结果集。我不知道这是否可以用 PHP 完成，但我认为可以。

Answer 4

由于类型未知，您可以通过扩展 PHP 上类似问题中的另一个答案来在 PHP 端使用一些分组： 对 While 循环中的记录进行分组

首先，请确保您对查询中的 id_contact 和类型进行排序：

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

然后在循环结果行时对行显示进行手动分组：

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