1

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?

  • Union and Group By are the first things to try (match the fields before the union) – Serpiton May 16 '14 at 13:07
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.