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