MySQL merge two tables with different data

Question

The following are my mysql tables

Table 1:

ID  | commonID | Date  | text | active
1     11         01.02   abc    1
2     11         02.02   123    1 
3     11         03.02   xyz    0

Table 2:

ID  | commonID | Date  | value | active
1     11         01.02   abc    1
2     11         04.02   123    1 
3     11         03.02   xyz    1

The Final result should display this:

| date | text |   value
  01.02  abc      abc
  02.02  123      (null)
  03.02  (null)   xyz
  04.02  (null)   123

The Idea here is, to merge the two tables. All entries with a defined commonID like 11 in the example will be selected from both tables. then the tables will be united. Conditions:

If there are matching dates in TABLE1 and TABLE2 they will be merged If there is a solo date in TABLE1 or TABLE2, the value/text for the table with no date will become NULL If there is a record in TABLE1 or TABLE2 that has active = FALSE, it will not be processed. There can be matching and not matching dates in BOTH tables.

I want to use this for display chronologic events, if there is an event in both tables, there should be only one line for this.

What could be the Solution here?

use full outer join..http://www.w3schools.com/sql/sql_join_full.asp — Charvee Shah, Feb 24 '15 at 10:10
alternative of its is union.http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — Charvee Shah, Feb 24 '15 at 10:12
yes, and it works if i join on the common id. but how to merge the dates? — sgt_johnny, Feb 24 '15 at 10:19

Raging Bull · Answer 1 · 2015-02-24T10:54:13.223

Try this:

SELECT T1.date,
       CASE WHEN T1.active = 1 THEN T1.text END as text,
       CASE WHEN T2.active =1 THEN T2.value END as value
FROM Table1 T1 LEFT JOIN
     Table2 T2 ON T1.date=T2.date
UNION
SELECT T2.date,
       CASE WHEN T1.active = 1 THEN T1.text END as test,
       CASE WHEN T2.active = 1 THEN T2.value END as value
FROM Table1 T1 RIGHT JOIN
     Table2 T2 ON T1.date=T2.date

Result:

DATE    TEXT    VALUE
01.02   abc     abc
02.02   123     (null)
03.02   (null)  xyz
04.02   (null)  123

Sample SQL Fiddle.

score 0 · Accepted Answer · answered Feb 24 '15 at 10:26

Try this:

SELECT t1.date,t1.text,t2.value FROM table1 t1
LEFT JOIN table2 t2 ON t1.commonId = t2.commonId and t1.date = t2.date and t2.active = 1
where t1.active = 1
UNION
SELECT t2.date,t1.text,t2.value FROM table2 t2
LEFT JOIN table1 t1 ON t1.commonId = t2.commonId and t1.date = t2.date and t1.active = 1
where t2.active = 1

Sample http://sqlfiddle.com/#!2/d2c4d/2

score 0 · Answer 3 · answered Feb 24 '15 at 10:59

Here's one way...

SELECT commonid
     , date
     , MAX(text) text
     , MAX(value) value
  FROM 
     ( SELECT id
            , commonid
            , date
            , text
            , NULL value
            , active
         FROM table1
        WHERE active <> 0

        UNION

       SELECT id
            , commonid
            , date
            , NULL
            , value
            , active
         FROM table2
        WHERE active <> 0
     ) x
 GROUP
    BY commonid,date;

You can move the WHERE active <> 0 bit out of the UNIONs if you like, to just before the GROUP BY.

MySQL merge two tables with different data

3 Answers3