MySQL query joining rows in same table

Question

I store sensor data in a MySQL table. The table has the fields id, timestamp, mac, temp and humidity.

Id is an unique autoincrement index, timestamp the time when the data was entered, Mac the Mac address of the sensor.

So for each sensor at each timestamp an entry is created. If all sensors are working the timestamp will be equal, but it can be that some sensors are not reachable.

I know want to query the temperatures of all sensors at all timestamps. For 3 sensors I came up with the following:

SELECT a.timestamp, a.temp, b.temp, c.temp from `data` as a 
inner join data as b on a.timestamp = b.timestamp
inner join data as c on a.timestamp = c.timestamp 
where a.mac = 'xx:xz:xz:34:03:F0' and 
b.mac = 'xz:xx:xx:34:C6:99' and 
c.mac='xx:xx:xx:32:27:36' order by a.timestamp

Sample data:

INSERT INTO `data` (`id`, `mac`, `timestamp`, `temp`, `humidity`, `battery`) VALUES
(132, '58:2D:34:32:27:36', '2020-02-01 19:30:11', '23.2', '53.5', '0.0'),
(133, '58:2D:34:34:C6:99', '2020-02-01 19:30:11', '23.1', '51.4', '0.0'),
(134, '58:2D:34:34:03:F0', '2020-02-01 19:30:11', '24.1', '50.0', '0.0'),
(138, '58:2D:34:32:27:36', '2020-02-01 19:40:08', '23.2', '54.3', '0.0'),
(139, '58:2D:34:34:C6:99', '2020-02-01 19:40:08', '23.2', '51.6', '0.0'),
(140, '58:2D:34:34:03:F0', '2020-02-01 19:40:08', '24.1', '50.1', '0.0'),
(141, '58:2D:34:32:27:36', '2020-02-01 19:50:08', '23.3', '54.6', '0.0'),
(142, '58:2D:34:34:C6:99', '2020-02-01 19:50:08', '23.2', '51.4', '0.0'),
(143, '58:2D:34:34:03:F0', '2020-02-01 19:50:08', '24.0', '50.3', '0.0');

Example output:

Timestamp.         , Mac1, Mac2, Mac3
2020-02-01 19:30:08, 24.0, 23.2, 23.0
2020-02-01 19:40:08, 24.0, 23.1, 23.0
2020-02-01 19:50:12, 24.1, 23.1, 23.0

But I think there should/could be a more efficient way?

Any ideas?

Thanks

It doesn't look like you posted a working query (typo in the second row). Perhaps you could provide some expected output to help clarify what you are looking for. My hunch is what you really want to do is GROUP BY timestamp and use GROUP_CONCAT to get the various sensor temps for that particular timestamp. In that scenario your WHERE clause would change to be where mac in ('mac1', 'mac2', ...). — totalhack
– totalhack, Commented Feb 8, 2020 at 18:30

totalhack · Accepted Answer · 2020-02-08 19:28:13Z

1

Another approach would be to group by timestamp and use GROUP_CONCAT to get the sensor data for that timestamp.

SELECT 
timestamp, 
GROUP_CONCAT(CONCAT(mac, ':', temp)) as temps
from data  
where mac in ('xx:xz:xz:34:03:F0', 'xz:xx:xx:34:C6:99', 'xx:xx:xx:32:27:36')
group by timestamp
order by timestamp

You'll also want an index on timestamp (even if you stick with your original approach). More info on the options for GROUP_CONCAT here.

edited Feb 8, 2020 at 19:28

answered Feb 8, 2020 at 18:35

totalhack

2,6781 gold badge24 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

totalhack Over a year ago

I see you've provided an example. GROUP_CONCAT will give you the result all in one column rather than broken into separate columns per mac (though you can format it in a readable way within that column). If you definitely need that other format then it's possible your first approach is the only way to do it, but that gets messy if your list of macs grows.

thefloe Over a year ago

I see. Just tried your suggestion and it returns indeed a single column but also a single row only... O.k. got it: group by instead of order by. Thanks that works!

totalhack Over a year ago

Whoops, the single row was because I forgot to add the group by statement...answer edited. Sorry about that. Glad it helped.

Collectives™ on Stack Overflow

MySQL query joining rows in same table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related