CODEWITHSUNDEEP
mysqlmerge
kami
kami

Reputation: 11

Merge two tables with different timestamps to one query output

I have got two tables like this:

Table 1:

    id  timestamp              Volume    value
    1   2015-02-17 14:11:10     1220.62     0
    2   2015-02-17 14:13:48     1220.62     0
    3   2015-02-17 14:16:39     1220.62     0
    4   2015-02-17 14:17:22     1220.62     0
    5   2015-02-17 14:17:47     1220.62     0

Table 2:

    id  TimeDate    aussentemp  
    1   2015-02-17 14:11:15     0 
    2   2015-02-17 14:13:03     22.9 
    3   2015-02-17 14:16:04     23
    4   2015-02-17 14:17:02     22.9 
    5   2015-02-17 14:17:03     23

You can see the timestamps are nearly the same. Just want to merge this, that the SQL Query use the timestamp and data from table 1 and add a new columns with the "aussentemp" that is nearly there with the same timestamp.

Can someone help me?

Upvotes: 1

Views: 797

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

Here is one method using a correlated subquery:

select t1.*,
       (select t2.aussentemp
        from table2 t2
        where t2.timedate <= t1.timedate
        order by t2.timedate desc
        limit 1
       ) as aussentemp
from table1 t1;

Note: this takes the most recent value on or before the timestamp in table1.

If you also need the timestamp, you can repeat the subquery.

Upvotes: 1

Related Questions