CODEWITHSUNDEEP
mysql
KimBenGon
KimBenGon

Reputation: 315

MySQL 2 tables UNION

I ask for your understanding because I can not speak English well.

There are 4 tables. 

sensor (PK: sensor)
sensor | service
   1   |    1
   2   |    2

equipment (PK: id, FK: sensor)
id | equip | sensor | 
 1 |   8   |    1   |
 2 |   8   |    1   |
 3 |   8   |    2   |
 4 |   7   |    2   |

A (PK: AUTO INCREMENT, UNIQUE: id, time, FK: id)
id |   time | temperature
 1 |  1027  | 30
 1 |  1028  | 30
 1 |  1029  | NULL
 1 |  1030  | 60
 1 |  1101  | 999

B (PK: AUTO INCREMENT, UNIQUE: id, time, FK: id)
id |   time | temperature
 2 | 1027   | 40
 2 | 1029   | 50
 2 | 1030   | NULL
 2 | 1031   | 59

I want the following results. 

time | A_temperature | B_temperature
1027 | 30            | 40
1028 | 30            | NULL
1029 | NULL          | 50
1030 | 60            | NULL
1031 | NULL          | 59

So I made the following query:

SELECT DISTINCT COALESCE(A.time, B.time) time, A.temperature AS A_temperature, B.temperature AS B_temperature
FROM equipment AS equip
JOIN sensor sen
    ON  sen.sensor = equip.sensor
   AND sen.service = 1
JOIN A
    ON A.id = equip.id
    AND (A.time>= '1027' AND A.time<= '1031')  
LEFT JOIN B
   ON B.id = equip.id
   AND (B.time>= '1027' AND B.time<= '1031')
   AND B.time= A.time
WHERE equip.equip = 8

UNION

SELECT DISTINCT COALESCE(B.time, A.time) time, A.temperature AS A_temperature, B.temperature AS B_temperature
FROM equipment AS equip
JOIN sensor sen
    ON sen.sensor = equip.sensor
   AND sen.service = 1
LEFT JOIN B
   ON B.id = equip.id
   AND (B.time>= '1027' AND B.time<= '1031')
   AND B.time= A.time
JOIN A
   ON A.id = equip.id
   AND (A.time>= '1027' AND A.time<= '1031')  
WHERE equip.equip = 8
ORDER BY time ASC;

But I did not get the results I wanted. 

time | A_temperature | B_temprature
1027 |      30       |    NULL
1027 |     NULL      |     40
1028 |      30       |    NULL
1028 |     NULL      |    NULL
1029 |     NULL      |     50
 ....

After executing the above query, A.time and B.time are separated and the result is output. I want to combine these at the same time. If time is null, we want to put it in non-null time.

Upvotes: 1

Views: 42

Answers (1)

Nick
Nick

Reputation: 147166

I think you can simplify the answer from your previous question by creating a subquery which just selects all the distinct time values from tables A and B. Then you can separately JOIN the A and B tables to it and GROUP BY the time values, using MAX to aggregate the temperatures since the values will be either valid or NULL, which MAX will ignore:

SELECT t.time, MAX(A.temperature) AS A_temperature, MAX(B.temperature) AS B_temperature
FROM equipment e
JOIN sensor s ON s.sensor = e.sensor AND s.service = 1
JOIN (SELECT time FROM A UNION SELECT time FROM B) t
LEFT JOIN A on A.id = e.id AND A.time = t.time
LEFT JOIN B on B.id = e.id AND B.time = t.time
WHERE t.time BETWEEN 1027 AND 1031
GROUP BY t.time
ORDER BY t.time

Output:

time    A_temperature   B_temperature
1027    30              40
1028    30  
1029                    50
1030    60  
1031                    59

Demo

Upvotes: 1

Related Questions