CODEWITHSUNDEEP
mysql
Michael
Michael

Reputation: 5335

Error when joining a table to itself

I have a table where the data of measured temperature and humidity is stored. Records with temperature have the field meas_kind set to "T", and for humidity it is set to "H". 

mysql> describe meteo;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| meas_time | timestamp           | YES  |     | NULL    |                |
| room      | varchar(10)         | NO   |     | NULL    |                |
| meas_kind | char(1)             | NO   |     | NULL    |                |
| value     | double              | NO   |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+

mysql> select * from meteo;
+----+---------------------+------+-----------+-------+
| id | meas_time           | room | meas_kind | value |
+----+---------------------+------+-----------+-------+
| 35 | 2017-05-24 16:51:47 | 123  | T         | 22.61 |
| 36 | 2017-05-24 16:51:47 | 123  | H         | 36.93 |
| 37 | 2017-05-24 16:51:51 | 123  | T         | 22.61 |
| 38 | 2017-05-24 16:51:51 | 123  | H         | 36.94 |
| 39 | 2017-05-24 16:51:56 | 123  | T         | 22.61 |
| 40 | 2017-05-24 16:51:56 | 123  | H         | 36.94 |
+----+---------------------+------+-----------+-------+

Temperature and humidity are measured in the same time, so I want this table to be like this:

+---------------------+------+-------+-------+
| meas_time           | room | Temp  | Humid |
+---------------------+------+-------+-------+
| 2017-05-24 16:51:47 | 123  | 22.61 | 36.93 |
| 2017-05-24 16:51:51 | 123  | 22.61 | 36.94 |
| 2017-05-24 16:51:56 | 123  | 22.61 | 36.94 |
+---------------------+------+-------+-------+

I've tried to make this query, but it gives me an error:

ERROR 1242 (21000): Subquery returns more than 1 row

Please help me to get a correct result.

select 
    m.meas_time, 
    m.room, 
    (select value from meteo m1 where m1.meas_kind='T' and m.meas_time=m1.meas_time) as Temp, 
    (select value from meteo m2 where meas_kind='H' and m.meas_time=m2.meas_time) as Humid 
from meteo m 
     join meteo m1 
         on m.meas_time=m1.meas_time 
         and m.room=m1.room 
     join meteo m2 
         on m.meas_time=m2.meas_time 
         and m.room=m2.room 
group by m.room, m.meas_time;

Upvotes: 0

Views: 38

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521399

You are overcomplicating things. A simple pivot query can give you the results you want.

SELECT
    meas_time,
    room,
    MAX(CASE WHEN meas_kind = 'T' THEN value END) AS Temp,
    MAX(CASE WHEN meas_kind = 'H' THEN value END) AS Humid
FROM meteo
GROUP BY meas_time, room

Upvotes: 1

Related Questions