full join of 2 tables with common time

Question

how am i able to join the 2 tables (A & B) below so that i can get the resulting table below

Please note that the query should join values that have the same time,

(i.e. 2014-11-29 9:58:23 6054 1 showing below in the result table)

Table A

ID      time                noise
76676   2014-11-29 09:55:24 6636
76677   2014-11-29 09:55:32 5256
76678   2014-11-29 09:55:42 5066
76679   2014-11-29 09:56:09 11560
76680   2014-11-29 09:56:22 5656
76681   2014-11-29 09:56:50 7031
76682   2014-11-29 09:56:56 5069
76683   2014-11-29 09:57:06 10088
76684   2014-11-29 09:57:23 5589
76685   2014-11-29 09:58:23 6054

Table B

ID  time                movement
789 2014-11-29 09:57:04 1
790 2014-11-29 09:57:10 1
791 2014-11-29 09:57:30 1
792 2014-11-29 09:57:34 1
793 2014-11-29 09:57:40 1
794 2014-11-29 09:57:43 1
795 2014-11-29 09:57:52 1
796 2014-11-29 09:58:09 1
797 2014-11-29 09:58:23 1
798 2014-11-29 09:58:31 1

Result:

time                noise   movement
2014-11-29 9:55:24  6636    null
2014-11-29 9:55:32  5256    null
2014-11-29 9:55:42  5066    null
2014-11-29 9:56:09  11560   null
2014-11-29 9:56:22  5656    null
2014-11-29 9:56:50  7031    null
2014-11-29 9:56:56  5069    null
2014-11-29 9:57:04  null    1
2014-11-29 9:57:06  10088   null
2014-11-29 9:57:10  null    1
2014-11-29 9:57:23  5589    null
2014-11-29 9:57:30  null    1
2014-11-29 9:57:34  null    1
2014-11-29 9:57:40  null    1
2014-11-29 9:57:43  null    1
2014-11-29 9:57:52  null    1
2014-11-29 9:58:09  null    1
2014-11-29 9:58:23  6054    1
2014-11-29 9:58:31  null    1

You need to perform a full outer join, something that is not directly supported on MySQL, but can be easily emulated with the help of a UNION or UNION ALL, as seen here: http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — jynus, Nov 28 '14 at 23:45
i used the following, however the result are separated, i.e the time from table A is in a different column to the time from Table B. SELECT * FROM noise LEFT JOIN movement ON noise.time = movement.time UNION SELECT * FROM noise RIGHT JOIN movement ON noise.time = movement.time — Ossama, Nov 29 '14 at 00:00

Rahul · Accepted Answer · 2014-11-29T00:20:47.730

1

You need to perform a FULL OUTER JOIN as already commented by Jynus.

select t1.time,t1.noise,t2.movement 
from TableA t1 left join TableB t2
on t1.time = t2.time
UNION
select t1.time,t1.noise,t2.movement 
from TableA t1 right join TableB t2
on t1.time = t2.time;

Per your last comment: that's correct and it shouldn't since that row is coming from a RIGHT JOIN but you can tweak it a bit using CASE expression like

case when t1.time is null then t2.time else t1.time end as time

Or just use t2.time in the RIGHT JOIN query as pointed by @Karlois

See a demo here http://sqlfiddle.com/#!2/d42d01/2

SELECT * FROM
(
select t1.time,t1.noise,t2.movement 
from TableA t1 left join TableB t2
on t1.time = t2.time
UNION
select t2.time,
t1.noise,t2.movement 
from TableA t1 right join TableB t2
on t1.time = t2.time
) tab
ORDER BY time;

edited Nov 29 '14 at 00:20

answered Nov 28 '14 at 23:39

Rahul

76,197
13
71
125

Thanks, this partly works, but it does not show the condition: 2014-11-29 9:58:31 null 1 where the noise is null, do you know what i mean – Ossama Nov 28 '14 at 23:44
thanks mate, this works, however it does not show the time where noise = null. i.e. null null 1 – Ossama Nov 28 '14 at 23:54
You don't need `CASE` condition, just use `t2.time` instead of `t1.time` in the second SELECT statement – Karolis Nov 29 '14 at 00:09
Great, very close, we just need to sort the time – Ossama Nov 29 '14 at 00:10
@Ossama, just add a `order by` in the outer query. – Rahul Nov 29 '14 at 00:15

score 1 · Answer 2 · answered Nov 29 '14 at 00:13

First you need to union time of both tables and then create the join with both table to get noise and movment

SELECT ts.time, ta.noise, tb.movement 
  FROM (SELECT a.time from tablea a
        UNION
        SELECT b.time from tableb b) ts 
    LEFT OUTER JOIN tablea ta ON ts.time = ta.time
    LEFT OUTER JOIN tableb tb ON ts.time = tb.time

score 0 · Answer 3 · answered Nov 29 '14 at 01:55

I like the union and aggregate approach to full outer joins:

select time, max(noise) as noise, max(movement) as movement
from ((select a.time, a.noise, NULL as movement
       from tablea a
      ) union all
      (select b.time, NULL, b.movement
       from tableb b
      )
     ) ab
group by time;

full join of 2 tables with common time

3 Answers3