Complex Two Table Join

Question

Trying to a result set that has up to one row per Sensorrule row where Sensorrule.sensorid exists in Sensordata.sensorid with the DATE(Sensor.messagedate) = CURDATE() and the TIME(Sensordata.messagedate) => Sensorrule.fromtime and TIME(Sensordata.messagedate) => Sensorrule.totime.

Resulting set could be less than number of rows in Sensorrule if there are no rows in Sensordata with the current date (messagedate) with the time of messagedate between fromtime and totime.

Sensorrule
+-------------+--------------+------------+-------+--------------+
| sensorid    | fromtime     | totime     | msg   | name         |
+-------------+--------------+------------+-------+--------------+
| 27460       | 5:00         | 11:00      | Work  | Bob          |
| 27461       | 4:00         | 15:00      | Not   | Susan        |
| 27460       | 7:30         | 12:00      | Max   | Rick         |
+-------------+--------------+------------+-------+--------------|

Sensordata
+---------+---------------------+---------------+----------+
| Room    | messagedate         | transactionid |  sensorid|
+---------+---------------------+---------------+----------+
| Bedroom | 2014-05-30 07:48:14 | 332243181     | 27460    |
| Bedroom | 2014-05-30 05:48:14 | 332243182     | 27464    |
| Bedroom | 2014-05-22 07:36:21 | 332243183     | 27460    |
| Bedroom | 2014-05-22 09:23:21 | 332656534     | 27464    |
| Bedroom | 2014-05-21 06:34:22 | 332243185     | 27461    |
| Bedroom | 2014-05-20 06:15:06 | 332243187     | 27460    |
| Bedroom | 2014-05-20 07:40:34 | 332243172     | 27465    |
| Bedroom | 2014-05-19 09:27:56 | 332243191     | 27461    |
| Bedroom | 2014-05-18 06:34:08 | 332243101     | 27460    |
+---------+---------------------+---------------+----------+

Would like the resulting set to look something like:

Sensorid    msg     name    
24760       Work    Bob 
27461       Not     Susan

Note: Data set above doesn't specifically yield the above result

Please show the table structure and tell us what you are trying to achieve? — user1613360, Jun 03 '14 at 02:04
What you want in result set I mean what is the select condition? — user1613360, Jun 03 '14 at 02:21
If the sensorid from each row in Sensorrule is found in one or more rows of Sensordata table with today's date (messagedate) and the time in messagedate is between Sensorrule's fromtime and totime then output Sensorrule's sensorid, MSG and name. I only want to see one line output (or none) for each row in Sensorrule if that condition exists at least once in Sensordata. — user3701363, Jun 03 '14 at 03:13

user1613360 · Accepted Answer · 2014-06-03T04:42:11.750

0

select distinct s.sensorid,s.msg,s.name from sensorrule s,sensordata d
where s.sensorid=d.sensorid and 
time(d.messagedate)>s.fromtime and 
time(d.messagedate)<s.totime;

edited Jun 03 '14 at 04:42

answered Jun 03 '14 at 03:18

user1613360

1,280
3
16
42

(MYSQL)ERROR 1271 (HY000): Illegal mix of collations for operation '>' – user3701363 Jun 03 '14 at 03:48
Was also getting this error message in my previous attempts (thus the ask for help here) – user3701363 Jun 03 '14 at 03:54
The problem is with your tables collation types.Check this http://stackoverflow.com/questions/1241856/illegal-mix-of-collations-error-in-mysql. – user1613360 Jun 03 '14 at 04:10
I think I found the problem. Changed s.fromtime and s.totime to explicit datetime format: e.g. STR_TO_DATE(s.totime,'%H:%i') That allows the query to work. However I get many results back (one for each row match in Sensordata rather than just one row for each row in Sensorrule. – user3701363 Jun 03 '14 at 04:12
Doesn't that just return one result rather than one possible result for each row in Sensorrule? – user3701363 Jun 03 '14 at 04:25

Complex Two Table Join

1 Answers1