How to prevent GROUP_CONCAT from creating a result when no input data is present (with UNION)?

48 Views Asked by At

How can I make sure my query doesn't return null rows?

MYSQL Query:

SELECT ts, 
GROUP_CONCAT(if(sensor_id = 197, temp, NULL)) AS '197',
GROUP_CONCAT(if(sensor_id = 198, temp, NULL)) AS '198',
GROUP_CONCAT(if(sensor_id = 199, temp, NULL)) AS '199'
FROM table_1
WHERE ts BETWEEN 1609435800 AND 1620115200 
AND sensor_id IN (197,198,199)  

UNION

SELECT ts, 
GROUP_CONCAT(if(sensor_id = 197, temp, NULL)) AS '197',
GROUP_CONCAT(if(sensor_id = 198, temp, NULL)) AS '198',
GROUP_CONCAT(if(sensor_id = 199, temp, NULL)) AS '199'
FROM table_2  
WHERE ts BETWEEN 1609435800 AND 1620115200 
AND sensor_id IN (197,198,199) 

GROUP BY ts ORDER BY ts ASC 

This query returns this result:

+------------+--------------------+------+--------------------+
| ts         | 197                | 198  | 199                |
+------------+--------------------+------+--------------------+
|       NULL | NULL               | NULL | NULL               |
| 1620113100 | NULL               | NULL | NULL               |
| 1620114000 | NULL               | NULL | 20.152999877929688 |
| 1620114300 | 19.995166778564453 | NULL | 20.354490280151367 |
| 1620114600 | 20.103750228881836 | NULL | 20.42103385925293  |
| 1620114900 | 20.34025001525879  | NULL | NULL               |
+------------+--------------------+------+--------------------+

There seems to be a null row returned whenever there are no results to be returned from first table. I do not get a null row from table_2 though when there aren't results from this table because of that "GROUP BY" clause. How can I make sure I don't get null rows?

0

There are 0 best solutions below