Mysql – merge 3 thesql tables into one with no duplicates

distinctMySQLselectunion

I would like to merge 3 tables data, each have a number column in common,
but all the other data differs.

What I would like to achieve is selecting all of the data from the common column
and merging the rest of the columns from all the tables based on the number column.

Please keep in mind that the number column can have duplicates between the tables.

t1
------------------------------
| number | stuff 1 | stuff 2 |
------------------------------
|   1    |    x    |    y    |
------------------------------

t2
-------------------------------------------------------
| number | data 1 | data 2 | data 3 | data 4 | data 5 |
-------------------------------------------------------
|   2    |    a   |   b    |   c    |   d    |   e    |
-------------------------------------------------------

t3
----------------------------------------------
| number | info 1 | info 2 | info 3 | info 4 |
----------------------------------------------
|   1    |   j    |   k    |   l    |   m    |
----------------------------------------------
|   3    |   j    |   k    |   l    |   m    |
----------------------------------------------

Merged Table
------------------------------------------------------------------
| number | stuff 1 | stuff 2 | data 2 | data 5 | info 1 | info 4 |
------------------------------------------------------------------
|   1    |    x    |    y    |        |        |   l    |   m    |
------------------------------------------------------------------
|   2    |         |         |    b   |    e   |        |        |
------------------------------------------------------------------
|   3    |         |         |        |        |    l   |    m   |
------------------------------------------------------------------

There must be no duplicates of the actual number, but any data across the 3 tables for that number must be merged into one row.

What would be a bonus is if it could do ON DUPLICATE KEY UPDATE, So I can run this query once every few days to add any new data

Thank you in advance

Best Answer

SELECT tt0.number
     , tt1.`stuff 1`
     , tt1.`stuff 2`
     , tt2.`data 2`
     , tt2.`data 5`
     , tt3.`info 1`
     , tt3.`info 4`
FROM (   SELECT t1.number FROM t1
       UNION DISTINCT
         SELECT t2.number FROM t2
       UNION DISTINCT
         SELECT t3.number FROM t3
     ) tt0
LEFT JOIN t1 tt1 ON tt0.number = tt1.number
LEFT JOIN t2 tt2 ON tt0.number = tt2.number
LEFT JOIN t3 tt3 ON tt0.number = tt3.number

This query assumes the number field is primary or at least unique key in each table.

UPDATE:

Unfortunately this gave me errors saying that there were duplicate entries for number

If You got error You describe that is NOT MySQL error.

http://www.sqlfiddle.com/#!9/22d82dc/1

Related Question