CODEWITHSUNDEEP
sqltimestamp
Rodrigo Merino
Rodrigo Merino

Reputation: 11

sql: combine two tables around timestamp

I have two tables with sensor data and I want to combine both tables into one.

Table 1:

Sensor1 | Timestamp
34        2018-22-05 23:23:34
25        2018-22-05 22:23:23
51        2019-22-05 12:34:56

Table 2:

Sensor2 | Timestamp
27        2018-22-05 23:37:33
29        2018-22-05 22:36:47
66        2019-22-05 12:34:56

And my desired result is:

Expected Table:

Sensor1 | Sensor2 | Timestamp
          27        2018-22-05 23:37:33
34                  2018-22-05 23:23:34
          29        2018-22-05 22:36:47
25                  2018-22-05 22:23:23
51        66        2019-22-05 12:34:56

It is very unlikely that we could have the same timestamp in both tables, but even if we do, it shouldn't be a problem to have values in both Sensor colums.

I have tried with UNION, UNION ALL, OUTER JOIN, but I always end up with two timestamp columns.

Has anybody an idea?

Thanks and best regards, Rodrigo.

Upvotes: 1

Views: 1271

Answers (4)

Rhys Jones
Rhys Jones

Reputation: 5508

Depending on what flavour of SQL you're using the simplest way of doing this might be to use a FULL OUTER JOIN, something like this;

select
    t1.Sensor1,
    t2.Sensor2,
    coalesce(t1.Timestamp, t2.Timestamp) as Timestamp
from 
    table1 t1 full outer join table2 t2 on t2.Timestamp = t1.Timestamp

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

If no duplicates appear within a table and you want to combine results from both tables, I would suggest aggregation:

select max(sensor1) as sensor1, max(sensor2) as sensor2, timestamp
from ((select Sensor1, null as Sensor2, timestamp from t1
      ) union all
      (select null, Sensor2, timestamp
       from t2
      )
     ) t
group by timetamp
order by timestamp desc;

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can try this query.

just use UNION ALL to combine two tables.

  • Table1 set Sensor2 column be NULL
  • Table2 set Sensor1 column be NULL

then order by Timestamp

SELECT * FROM (
select Sensor1 'Sensor1',null 'Sensor2',Ti from T 
UNION ALL 
select null 'Sensor1', Sensor1 'Sensor2',Ti from T2 
) t
order by ti desc

sqlfiddle: http://sqlfiddle.com/#!9/fb22b/9

OUTPUT

| Sensor1 | Sensor2 |                   Ti |
|---------|---------|----------------------|
|  (null) |      27 | 2018-05-22T23:37:33Z |
|      34 |  (null) | 2018-05-22T23:23:34Z |
|  (null) |      29 | 2018-05-22T22:36:47Z |
|      25 |  (null) | 2018-05-22T22:23:23Z |

if you want to let NULL be empty string. you can use coalesce function.

SELECT coalesce(Sensor1,'') as 'Sensor1',
       coalesce(Sensor2,'') as 'Sensor2',
       ti
FROM (
select Sensor1 'Sensor1',null 'Sensor2',Ti from T 
UNION ALL 
select null 'Sensor1', Sensor1 'Sensor2',Ti from T2 
) t
order by ti desc

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

select * from (
  select sensor1, null as sensor2, timestamp from table1
    where timestamp not in (select timestamp from table2)
  union all
  select null, sensor2, timestamp from table2
    where timestamp not in (select timestamp from table1)
  union all
  select t1.sensor1, t2.sensor2, t1.timestamp from table1 t1
    join table2 t2 on t1.timestamp = t2.timestamp
  ) a
order by timestamp;

Upvotes: 0

Related Questions