mysql - 用两个外键联接两次表

我想提取有关存储在数据库中的人员和公司的信息。我有2个表（客户和联系人），它们指向包含2个外键（addressClientID和addressContactID）的唯一地址表。
客户可以有多个地址，“联系人”通常是一个，它可以是自定义地址（addressContactID将指向联系人ID，否则为NULL）或正在工作的客户的地址。
查询应提取联系人的姓名，该联系人正在工作的公司的名称以及城市。后者必须是公司所在的城市（如果addressContactID为NULL）或联系人所在的城市（如果addressContactID指向联系人ID）。

我尝试了多种类型的联接，但均未成功。 DBMS是MySql 5.2

mysql> select * from Clients;
+----+---------+-------------+--------+
| id | name    | taxCode     | optOut |
+----+---------+-------------+--------+
|  1 | Client1 | 51824753556 |      0 |
|  2 | Client2 | 51824543536 |      0 |
+----+---------+-------------+--------+
2 rows in set (0.01 sec)

mysql> select * from Contacts;
+----+--------------+---------------+------------------------+------------------+------------------+--------------+-------+------------------+
| id | name         | preferredName | email                  | mobile           | phone            | jobTitle     | notes | contactsClientId |
+----+--------------+---------------+------------------------+------------------+------------------+--------------+-------+------------------+
|  1 | Contact 1    | NULL          | [email protected]     | [phone]   | [phone] | Title1       | NULL  |                1 |
|  2 | Contact 2    | NULL          | [email protected]     | [phone] | [phone]   | Title2       | NULL  |                2 |
|  4 | Contact 3    | NULL          | [email protected]    | NULL             | NULL             | Title3       | NULL  |                1 |
+----+--------------+---------------+------------------------+------------------+------------------+--------------+-------+------------------+
3 rows in set (0.00 sec)

mysql> select * from Address;
+----+-------------+---------+----------+-------+-----------+-----------------+------------------+---------------------+
| id | address     | city    | postcode | state | country   | addressClientId | addressContactId | addressEngagementId |
+----+-------------+---------+----------+-------+-----------+-----------------+------------------+---------------------+
|  1 | Address n.1 | Sydney  | 2000     | NSW   | Australia |               1 |             NULL |                NULL |
|  2 | Address n.2 | Adelaide| 2010     | NSW   | Australia |               2 |             NULL |                NULL |
| 19 | Address n.3 | Perth   | 2050     | NSW   | Australia |               1 |                4 |                NULL |
+----+-------------+---------+----------+-------+-----------+-----------------+------------------+---------------------+
3 rows in set (0.01 sec)

预期的结果是这样的

ContactName, CompanyName, City

Contact 1, Client1, Sydney
Contact 2, Client1, Adelaide
Contact 3, Client2, Perth

最佳答案

这是我想出的：

SELECT co.name, cl.name, IF(a1.id IS NULL, a2.city, a1.city) AS city
FROM Contacts co
JOIN Clients cl
     ON co.contactsClientId = cl.id
LEFT JOIN Address a1
     ON co.id = a1.addressContactId
LEFT JOIN Address a2
     ON cl.id = a2.addressClientId
     AND a1.id IS NULL
     AND a2.addressContactId IS NULL
ORDER BY co.name

但是请注意，您的Contact 1适用于具有两个地址的Client1。因此，在这种情况下，我添加了AND a2.addressContactId IS NULL行以仅选择一个未分配addressContactId的地址，但是根据您的实际数据，您仍然可能会重复（如果您有一个联系人为一家公司提供两个地址，但没有，例如）

关于mysql - 用两个外键联接两次表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57490542/