1

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

1
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

| improve this answer | |
  • Unfortunately this gave me errors saying that there were duplicate entries for number, I will post what I came up with that seems to work – Sayajin May 20 '18 at 9:58
0

Having schema more or less like this:

create table t1 (
number1 text,
stuff1 text,
stuff2 text);

insert into t1 (number1,stuff1,stuff2) values ('1', 'x', 'y');

create table t2 (
number1 text,
data1 text,
data2 text,
data3 text,
data4 text,
data5 text);

insert into t2 (number1,data1,data2,data3,data4,data5) values('2','a','b','c','d','e');

create table t3 (
number1 text,
info1 text,
info2 text,
info3 text,
info4 text);

insert into t3 (number1,info1,info2,info3,info4) values ('1','j','k','l','m');
insert into t3 (number1,info1,info2,info3,info4) values ('3','j','k','l','m');

you can achive exactly what you want with this query:

select x.number1,max(stuff1) stuff1,max(stuff2) stuff2,max(data1) data1,max(data2)data2,max(data3) data3,max(data4) data4,max(data5) data5,max(info1) info1,max(info2) info2,max(info3) info3,max(info4) info4 
from (
select number1,stuff1,stuff2,null data1,null data2,null data3,null data4,null data5,null info1, null info2, null info3, null info4
from t1
union
select number1,null stuff1, null stuff2,data1,data2,data3,data4,data5,null info1, null info2, null info3, null info4
from t2
union
select number1,null stuff1, null stuff2,null data1,null data2,null data3,null data4,null data5,info1, info2, info3, info4
from t3) x
group by x.number1;

The result is:

+---------+--------+--------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| number1 | stuff1 | stuff2 | data1 | data2 | data3 | data4 | data5 | info1 | info2 | info3 | info4 |
+---------+--------+--------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| 1       | x      | y      | NULL  | NULL  | NULL  | NULL  | NULL  | j     | k     | l     | m     |
| 2       | NULL   | NULL   | a     | b     | c     | d     | e     | NULL  | NULL  | NULL  | NULL  |
| 3       | NULL   | NULL   | NULL  | NULL  | NULL  | NULL  | NULL  | j     | k     | l     | m     |
+---------+--------+--------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.