MySQL – How to Make Multiple Joins on the Same Table

join;mysql-5.5

I have a table (signal) which contains references to two other tables (ee and eeav).
I want to perform an SQL Query which combines the data from ee and eeav tables.

The situation is as follows:
The table signal can either have a reference to ee or eeav. In other words: when ee has a non-null value, eeav has the value null, and vice versa.
The table eeav has a reference to an ee.

These are the tables:

Table: Signal

+-----------+------+-------+
| s         |      |       |
+-----------+------+-------+
| s.id      | s.ee | s.eeav|
| 1         | 20   | null  |
| 2         | null | 500   |
| 3         | 40   | null  |
| 4         | null | 700   |
+-----------+------+-------+

Table: EquipmentEntity

+-------+---------+
|  ee   |         |
+-------+---------+
| ee.id | ee.name |
| 20    | ee20    |
| 30    | ee30    |
| 40    | ee40    |
| 50    | ee50    |
| 100   | ee100   |
| 101   | ee101   |
| 102   | ee102   |
| 103   | ee103   |
+-------+---------+

Table: EquipmentEntityActualValue

+---------+-----------+---------+
|  eeav   |           |         |
+---------+-----------+---------+
| eeav.id | eeav.name | eeav.ee |
| 400     | eeav400   | 100     |
| 500     | eeav500   | 101     |
| 600     | eeav600   | 102     |
| 700     | eeav700   | 103     |
+---------+-----------+---------+

Desired result

+----------+-----------+---------+
| s.id     | eeav.name | ee.name |
+----------+-----------+---------+
| 1        | null      | ee20    |
| 2        | eeav500   | ee101   |
| 3        | null      | ee40    |
| 4        | eeav700   | ee103   |
+----------+-----------+---------+

I am able to combine the signal table with the eeav table (including the ee) as follows:

SELECT s.Id, eeav.Name, ee.Name FROM signal s
INNER JOIN equipmententityactualvalue eeav ON (s.Eeav = eeav.Id)
INNER JOIN equipmententity ee ON (eeav.Ee = ee.Id)

I also managed to combine the signal table with the ee table:

SELECT s.Id, ee.Name FROM signal s
INNER JOIN equipmententity ee ON (s.Ee = ee.Id)

How can I "combine" these two results, so that I get the result as visualized in the "desired result" table?

Best Answer

I think this does what you need

 SELECT s.Id, eeav.Name, IFNULL(ee.Name, ee2.Name) AS 'Name' FROM signal s
 LEFT JOIN equipmententityactualvalue eeav ON (s.Eeav = eeav.Id)
 LEFT JOIN equipmententity ee ON (eeav.Ee = ee.Id)
 LEFT JOIN equipmententity ee2 ON (s.Ee = ee2.Id)