Re: display multiple values, but only test for one

[Michael McKnight]

Hi Peter,

I corrected the query as you mentioned, it now reads:

select a.timest, a.devloc, a.value from sample a
join sample b on left(a.timest,10) = left(b.timest,10)
where left(a.timest,10) between '2018-01-04' and '2018-01-05' and b.devloc='hvac_main_supply' and b.value > 80 limit 300;

But instead of just printing values for "outside" and "hvac_main_supply" when hvac_main_supply > 80, its printing it for all devloc's, and the output is grouped by devloc. So, for example, it prints all of the den values and then all of the kitchen values, etc. But, the output for a given devloc repeats....

| 2018-01-04 05:47:01 | den | 70.2500 |
| 2018-01-04 05:47:01 | den | 70.2500 |
| 2018-01-04 05:47:01 | den | 70.2500 |
| 2018-01-04 05:47:01 | den | 70.2500 |
| 2018-01-04 05:47:01 | kitchen | 70.8125 |
| 2018-01-04 05:47:01 | kitchen | 70.8125 |
| 2018-01-04 05:47:01 | kitchen | 70.8125 |
| 2018-01-04 05:47:01 | kitchen | 70.8125 |

That's a snip -- each one repeats hundreds of times.


The create tables looks like this:

| Table | Create Table |
+--------+--------------+
| sample | CREATE TABLE `sample` (
`property` varchar(50) DEFAULT NULL,
`devloc` varchar(50) DEFAULT NULL,
`sensortype` varchar(50) DEFAULT NULL,
`timest` datetime DEFAULT NULL,
`value` decimal(8,4) NOT NULL,
KEY `property` (`property`),
KEY `devloc` (`devloc`),
KEY `sensortype` (`sensortype`),
KEY `timest` (`timest`),
KEY `value` (`value`),
KEY `timest_2` (`timest`,`devloc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1



The inserts look like this:
INSERT INTO sample (property,devloc,sensortype,timest,value)
VALUES ('$prop','$dev','$type','$time','$val')

Each sensor is read every 30 seconds and it's corresponding values are put into the database.

Thanks again for your help. I had no idea how complicated this would be.