SQLTeam.com | Weblogs | Forums

MySQL Union of multiple tables with different number of columns, ordered by another table to get the changed columns name

Hello,
I am trying to merge multiple tables, with a range of dates, together into one table. Most tables follow the same column names, but some tables don't have that column or their Names were changed. I want to use a Union query to multiple tables (base on column names) where we compare a one tables column name with another table (OLD and New column Names are compared in the initial table).
Exampe:
Multiple Tables for Union:
Table_1
|Date | New Name1 | New Name2 | OLD Name3 | New NameN |
|1/1/2008 | 73.2 | 43.2 | 33.2 | 13.2 |
|1/2/2008 | 13.2 | 23.2 | empty | 53.2 |
|1/3/2008 | 23.2 | 33.2 | 13.2 | 73.2 |
…………………………………………………………………………………….
|X/X/2008 | ……… | ……. | …….. | …….. |

Table_2
|Date | New Name1 | New Name2 | NEW Name3 | OLD Name4 | New NameN |
|1/1/2010 | 12.6 | 13.6 | 23.6 | 13.6 | 13.6 |
|1/2/2010 | 14.6 | 33.6 | 14.6 | 53.6 | 17.6 |
|1/3/2010 | 23.6 | 31.6 | empty | 77.6 | 16.6 |
………………………………………………………………………………………………………
|X/X/2010 | ……… | ……. | …….. | …….. | |

Table_3
|Date | New Name1 | New Name2 | NEW Name3 | NEW Name4 | New NameN |
|1/1/2013 | 12.2 | 19.2 | 24.2 | 17.2 | 14.2 |
|1/2/2013 | 24.2 | 23.2 | 15.2 | 43.2 | 17.2 |
|1/3/2013 | empty | 31.6 | 44.2 | 77.2 | 16.2 |
……………………………………………………………………………………………………………
|X/X/2013 | ……… | ……. | …….. | …….. | |

Table_N
|Date | New Name1 | New Name2 | NEW Name3 | OLD Name4 | New NameN |
|1/1/2018 | 32.4 | 13.4 | 23.4 | 43.4 | 13.4 |
|1/2/2018 | 14.4 | 33.4 | 14.4 | 53.4 | 17.4 |
|1/3/2018 | 23.4 | 31.4 | empty | 77.4 | 16.4 |
………………………………………………………………………………………………………
|X/X/2018 | ……… | ……. | …….. | …….. | |

Initial table: Column Names Comparison
|Name OLD | Name New |
|OLD Name1 | NEW Name1 |
|OLD Name2 | NEW Name2 |
|OLD Name3 | NEW Name3 |
|OLD Name4 | NEW Name4 |
……………………………………………...
|OLD NameN | NEW NameN |

Expected results of combination information from multiple MySQL tables. The columns of different tables are compared – if I can get them to match with the same name across multiple tables ordered by Initial Table (e.g. OLD Name3 = NEW Name3) the columns are United in the merged table; the columns that some table does not have are put into column list in merged table. Means the new created table should be a merged result of multiple tables based on their column name, containing all the data from same columns name and results come from the names of corresponding columns that not have the same names.

Merged Table
|Date | New Name1 | New Name2 | NEW Name3 = | OLD Name4 = | OLD NameN |
............................................................| OLD Name3 | New Name4 |
|1/1/2008 | 73.2 | 43.2 | 33.2 | 13.2 |………………… |
|1/1/2010 | 12.6 | 13.6 | 23.6 | 13.6 | 13.6 |
|1/3/2018 | 23.4 | 31.4 | empty | 77.4 | 16.4 |
…………………………………………………………………………………………………….…
|X/X/2018 | ……… | ……. | …….. | …….. | |

Thank you for your help!