Create mySQL Table from multiple mySQL tables; adding together identical columns and appending varchars

I have a more exotic SQL statement I'm trying to perform which "combines" 3 tables as 开发者_如何学Goa cartesion product and adds together the identical columns.

I've simplified this as much as possible. Say I've made three tables as such, which will then be combined to make table_d:

mysql>select * from table_a;
Code  Goat Dog Cat
A 4 5 6
B 7 8 9
C 10 11 12

mysql>select * from table_b;
Code  Goat Dog Cat
D 1 2 3
E 4 5 6
F 7 8 9

mysql>select * from table_c;
Code  Goat Dog Cat Bird
T 1 1 1 2
Y 2 2 2 3
U 3 3 3 4

An SQL create table statement, along the lines of "create table table_d as (select..." then makes a table like below.

Here the identically named columns are added together while the Code field is built up as a concatenated string. However I'm not sure how to go about this.

Thus

mysql>select * from table_d;
Code  Goat Dog Cat Bird
ADT 6 8 10 2
ADY 7 9 11 3
ADU 8 10 12 4
BDT 9 11 13 2  
BDY .....
....
....
CFU 20 22 24 4

Any advice or help is greatly appreciated at this point. This will also be performed on more than 3 tables at once but I showed only 3 here for simplicity. Thanks!

SQL Insertion code:::

create table table_a(code varchar(1),goat integer, dog integer, cat integer);
create table table_b(code varchar(1),goat integer, dog integer, cat integer);
create table table_c(code varchar(1),goat integer, dog integer, cat integer, bird integer);
insert into table_a values('A','4','5','6');
insert into table_a values('B','7','8','9');
insert into table_a values('C','10','11','12');
insert into table_b values('D','1','2','3');
insert into table_b values('E','4','5','6');
insert into table_b values('F','7','8','9');
insert into table_c values('T','1','1','1','2');
insert into table_c values('Y','2','2','2','3');
insert into table_c values('U','3','3','3','4');

---

Try this:

CREATE TABLE table_d
SELECT CONCAT(a.code, b.code, c.code) AS CODE, (a.goat + b.goat + c.goat) AS goat, (a.dog + b.dog + c.dog) AS dog, (a.cat + b.cat + c.cat) AS cat
FROM table_a a
JOIN table_b b
JOIN table_c c
ORDER BY CODE;

---

You might be getting into trouble because your column schema varies from table to table across a partitioned dataset. Relational DBs really prefer rows to columns when structure fluctuates. What about a more row-oriented model, like:

mysql>select * from table_a;
Code Type Number
A Goat 4
A Dog 5
A Cat 6
B Goat 7
B Dog 8
B Cat 9
C Goat 10
C Dog 11
C Cat 12

If you joined the tables with themselves multiple times then you would be able to use SUM() aggregate functions to do your counts rather than using calculated columns.