SQL Query to merge two tables with different timestamp as index

Question

I am not sure if my question will work with a sql query.

I have the two following Mysql tables:

Table a: The table get every 20 second a new Value

a.Time                  a.Value
2015-05-14 07:36:36     49
2015-05-14 07:36:56     49
2015-05-14 07:37:16     49
2015-05-14 07:37:36     49
2015-05-14 07:37:56     50
2015-05-14 07:38:16     50
2015-05-14 07:38:36     50
2015-05-14 07:38:56     49
2015-05-14 07:39:16     49
2015-05-14 07:39:36     49
2015-05-14 07:39:56     49
2015-05-14 07:40:16     50
2015-05-14 07:40:36     50
2015-05-14 07:40:56     50
2015-05-14 07:41:16     49
2015-05-14 07:41:36     49
2015-05-14 07:41:56     50
2015-05-14 07:42:16     50
2015-05-14 07:42:36     49
2015-05-14 07:42:56     49
2015-05-14 07:43:16     49
2015-05-14 07:43:36     49
2015-05-14 07:43:56     49
2015-05-14 07:44:06     49

Table b the table get every change of the value a new record

b.Time              b.Value
2015-05-14 07:34:50     1500
2015-05-14 07:38:52     2000
2015-05-14 07:40:52     0
2015-05-14 07:42:31     1500

The Result should like the following

a.Time                  a.Value     b.Time              b.Value
2015-05-14 07:36:36     49         2015-05-14 07:34:50  1500
2015-05-14 07:36:56     49         2015-05-14 07:34:50  1500
2015-05-14 07:37:16     49         2015-05-14 07:34:50  1500
2015-05-14 07:37:36     49         2015-05-14 07:34:50  1500
2015-05-14 07:37:56     50         2015-05-14 07:34:50  1500
2015-05-14 07:38:16     50         2015-05-14 07:34:50  1500
2015-05-14 07:38:36     50         2015-05-14 07:34:50  1500
2015-05-14 07:38:56     49         2015-05-14 07:38:52  2000
2015-05-14 07:39:16     49         2015-05-14 07:38:52  2000
2015-05-14 07:39:36     49         2015-05-14 07:38:52  2000
2015-05-14 07:39:56     49         2015-05-14 07:38:52  2000
2015-05-14 07:40:16     50         2015-05-14 07:38:52  2000
2015-05-14 07:40:36     50         2015-05-14 07:38:52  2000
2015-05-14 07:40:56     50         2015-05-14 07:40:52  0
2015-05-14 07:41:16     49         2015-05-14 07:40:52  0
2015-05-14 07:41:36     49         2015-05-14 07:40:52  0
2015-05-14 07:41:56     50         2015-05-14 07:40:52  0
2015-05-14 07:42:16     50         2015-05-14 07:40:52  0
2015-05-14 07:42:36     49         2015-05-14 07:42:31  1500
2015-05-14 07:42:56     49         2015-05-14 07:42:31  1500
2015-05-14 07:43:16     49         2015-05-14 07:42:31  1500
2015-05-14 07:43:36     49         2015-05-14 07:42:31  1500
2015-05-14 07:43:56     49         2015-05-14 07:42:31  1500
2015-05-14 07:44:06     49         2015-05-14 07:42:31  1500

The time-stamps in table a should with the next smaller time-stamp from table b!

Any idea how the query should look?

Thanks a lot!

Answer 1

Use subqueries with order by TIME and LIMIT 1 to get one top record from B with time less than current A.Time:

SELECT 
a.Time,
a.Value,
(SELECT Time FROM B WHERE B.Time<=A.Time ORDER BY B.Time DESC LIMIT 1) 
     as B_Time,
(SELECT Value FROM B WHERE B.Time<=A.Time ORDER BY B.Time DESC LIMIT 1)  
     as B_Value
FROM A
ORDER BY A.Time