I have four tables table_1, table_2, table_3 and table_4. All the four of them have columns like this:
table_1: age_grp, gender, height;
table_2: age_grp, gender, weight;
table_3: age_grp, gender, shoesize;
table_4: age_group, gender, BMI;
I would like to create a new table with columns:
age_grp, gender, height, weight, shoesize, BMI
I want only those columns to be merged where age_grp and gender is same in all tables. Any idea how to do this?
This easily can be doen via INNER JOIN:
SELECT table_1.*, table_2.*, table_3.*, table_4.* FROM table_1
INNER JOIN table_2 ON table_1.age_grp = table_2.age_grp
AND table_1.gender = table_2.gender
INNER JOIN table_3 ON table_2.age_grp = table_3.age_grp
AND table_2.gender = table_3.gender
INNER JOIN table_4 ON table_3.age_grp = table_4.age_grp
AND table_3.gender = table_4.gender
You can JOIN any table with any, if you have a requirement that all the data in all tables have same values in the columns.
Note that you shouldn't use * in real production script, use the column names explicitly.
JOINs can't be optimized easilly.age_grp gender height age_grp gender weight etc.There is a good chance you aren't going to get the results you want by a pure match. For example, the following will create the table you describe:
insert into newtable
select t1.age_grp, t1.gender, t1.height, t2.weight, t3.shoesize, t4.BMI
from table_1 t1
inner join table_2 t2 on t1.age_grp = t2.age_grp
and t1.gender = t2.gender
inner join table_3 t3 on t1.age_grp = t3.age_grp
and t1.gender = t3.gender
inner join table_4 t4 on t1.age_grp = t4.age_grp
and t1.gender = t4.gender;
The problem is if ANY of the items fail, you don't get a row. You might consider using outer join instead.
In spite of this request being answered already, I'd like to add an answer for the case of missing values, e.g. only shoesize not given for an age_grp/gender pair.
For a solution with joins you would need FULL OUTER JOINs which MySQL doesn't support. And mimicking this with LEFT and /or RIGHT OUTER JOINs can be a pain with several tables.
Here is a solution using UNION ALLs and a final aggregation instead.
create table mytable as
select age_grp, gender, max(height) as height, max(weight) as weight, max(shoesize) as shoesize, max(bmi) as bmi
from
(
select age_grp, gender, height, cast(null as unsigned integer) as weight, cast(null as unsigned integer) as shoesize, cast(null as unsigned integer) as bmi from table_1
union all
select age_grp, gender, cast(null as unsigned integer) as height, weight, cast(null as unsigned integer) as shoesize, cast(null as unsigned integer) as bmi from table_2
union all
select age_grp, gender, cast(null as unsigned integer) as height, cast(null as unsigned integer) as weight, shoesize, cast(null as unsigned integer) as bmi from table_3
union all
select age_group, gender, cast(null as unsigned integer) as height, cast(null as unsigned integer) as weight, cast(null as unsigned integer) as shoesize, bmi from table_4
) x
group by age_grp, gender;
I was surprised that CAST(NULL AS INT) results in a syntax error, btw. I had to change it to CAST(NULL AS UNSIGNED INTEGER).
SQL fiddle: http://www.sqlfiddle.com/#!2/f4fa5c/1.
