Christoph Mühlmann Christoph Mühlmann - 3 months ago 15
MySQL Question

MySQL: Merge two different tables without JOIN or UNION

I need to merge two tables:


  • Both have a primary key-column date, but with different values (different time intervals).

  • Both have different (unknown) columns: I don't know the names of the columns (same column-name may occur in both tables), I don't know how many columns, but all of the same type.



An example:

table1
date | colA | colB | colC
2011-02-02 | 1.09 | 1.03 | 1.04

table2
date | col1 | col2 | col3 | col4
2011-02-03 | 1.03 | 1.02 | 1.07 | 1.03


the result of the query should look like this:

tableResult
date | colA | colB | colC | col1 | col2 | col3 | col4
2011-02-02 | 1.09 | 1.03 | 1.04 | null | null | null | null
2011-02-03 | null | null | null | 1.03 | 1.02 | 1.07 | 1.03


This will not work:


  • INNER JOIN
    because it will only return the intersection between
    table1
    and
    table2
    ,

  • OUTER JOIN
    returns intersection + values only from left table (or right table if right join is used)

  • UNION
    because the count of columns may differ.



Any Ideas?

Christoph

Answer

You can create a temp table with the union of just the date column, and then use the temp table to left outer join with the other 2.

Example:

DROP TABLE temptbl IF EXISTS;
CREATE TEMPORARY TABLE temptbl (myDate DATETIME PRIMARY KEY)
    AS (SELECT MyDate FROM table1)
    UNION (SELECT MyDate FROM table2)
    ORDER BY MyDate;
SELECT * FROM temptbl
    LEFT OUTER JOIN table1 USING (MyDate)
    LEFT OUTER JOIN table2 USING (MyDate);