Join twice table with two foreign keys

I’d like to extract information about people and companies stored in my DB. I have 2 tables (Clients and Contacts) that point to a unique Address table containing 2 foreign keys (addressClientID and addressContactID). Clients can have multiple addresses, Contacts usually one and it’s either a custom address (addressContactID will be pointing to the contact id otherwise NULL) or the address of the Client who’s working for. The query should extract the name of the contact, name of the company the contact is working for and the city. The latter must be the city of the company (if the addressContactID is NULL) or the city of the contact (if addressContactID points to a contact id).

I’ve tried multiple types of joins with no success. The DBMS is 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          | contact1@gmail.com     | +61 421 111111   | +61 421 22222222 | Title1       | NULL  |                1 |
|  2 | Contact 2    | NULL          | contact2@gmail.com     | +61 421 15345431 | +61 421 263462   | Title2       | NULL  |                2 |
|  4 | Contact 3    | NULL          | contact3@fdsfsd.com    | 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)

The expected result is something like

ContactName, CompanyName, City

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

Answer

This is what I came up with:

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

Note however that your Contact 1 is working for Client1 which has two addresses. So I added the AND a2.addressContactId IS NULL line to only pick an address without assigned addressContactId in this case, but depending on your real data, you might still get duplicates (if you have one contact working for a company with two addresses without addressContactId, for example)

Advertisement

Answer