CODEWITHSUNDEEP
mysqlsqljoin
sgt_johnny
sgt_johnny

Reputation: 329

MySQL join 2 tables and unite a column

I have two tables:

Table A

ID  |  DATE     | VALUE | KEY|
1     30.8.14      100    11
2     25.8.14      500    11
2     20.8.14      250    11

Table B

ID  |  DATE     | VALUE | KEY|
1     30.8.14      AB     11
2     25.8.14      CD     11
3     10.8.14      EF     11

These two tables should be merged, key is used to define which entries should be merged WHERE KEY = '11'

IF there is a date in TABLE A that is also in TABLE B, it becomes on entry with both values IF there is no date in TABLE A that is also in TABLE B, the value for B becomes (null) And in the End, there should be only 1 date field. Columns should also be be a unique name..

I created this example table, how my output should look like

joinedDate   | aValue | bValue
30.8.14         100       AB
25.8.14         500       CD
20.8.14         250      (null)
10.8.14        (null)     EF

Im using MySQL version 5.5 on Maria DB

Could someone help me here?

Upvotes: 1

Views: 102

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13238

select a.date, a.value as avalue, b.value as bvalue
  from tablea a
  left join tableb b
    on a.date = b.date
union all
select b.date, null, b.value
  from tableb b
  left join tablea a
    on a.date = b.date
 where a.date is null

Fiddle: http://sqlfiddle.com/#!2/09ab9e8/4/0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270593

You seem to want full outer join, which MySQL doesn't offer. Here is one method:

select d.date, a.value as avalue, b.value as bvalue
from ((select date from a union
       select date from b
      )
     ) d left join
     a
     on a.date = d.date left join
     b
     on b.date = d.date;

Upvotes: 1

Related Questions