How to merge multiple mysql tables in one?


Problem :

I have a Mysql DB with 121 tables, with different structure, and I need to merge all of this tables in a 1 table.

There are 3 fields that are the same in all tables (email, base_name, location) but all other fields in some tables are the same, in others not.

Is there any wall to merge the tables and conservate all the fields (it doesn't matter if there is null fields)?

Thank you very much.



Solution :

I don't know if I understood well, but I guess your looking for joining tables and not merging them (sorry if i missunderstud). I put you an example of a left join, but if you want to keep every record from every table you'll need a full outer join (you'll have to simulate it in mysql cause it's not implemented)

CREATE TABLE table_name AS (

SELECT * 
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.email=t2.email AND t1.base_name=t2.base_name AND t1.location=t2.location
  ...
  LEFT JOIN tableN tN ON tN-1.email=tN.email AND tN-1.base_name=tN.base_name AND tN-1.location=tN.location

)

if you want to simulate a full outer join you should union like this:

SELECT * 
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.email=t2.email AND t1.base_name=t2.base_name AND t1.location=t2.location
  ...
  LEFT JOIN tableN tN ON tN-1.email=tN.email AND tN-1.base_name=tN.base_name AND tN-1.location=tN.location

UNION

SELECT * 
  FROM tableN tN
  LEFT JOIN tableN-1 tN-1 ON tN.email=tN-1.email AND tN.base_name=tN-1.base_name AND tN-1.location=tN.location
  ...
  LEFT JOIN table1 t1 ON t2.email=t1.email AND t2.base_name=t1.base_name AND t1.location=t2.location

Mysql Tutorials

Mysql Howto..

How can I delete 1 selected row from a lot of tables in PHP (from mysql)?

How to count rows in one table based on another table in mysql

How do I setup a Doctrine relation for a foreign key that could point to different tables, depending on context?

PHP How to check if a string already exists in a MySQL table

How to get and play WAV file stored as MySQL BLOB?

How to “remove duplicates” from a UNION query

How to manage a multi user job list in MySQL?

How to take mysql backup or Restore with 'SPACE' in the given path C#

How do I get multiple COUNT with multiple JOINS and multiple conditions?

How do I turn cascade on delete ON by default , for MySQL?

How to find time range in mysql given a table with “wall time”

How to Create Master script file to run mysql scripts

how to migrate a large database to new server

How to make a sub connection to mysql?

How to select from Db elements from one table which do not have connected elements with certain property

How to manage databases with limited amounts of data

How to insert Primary Key value of the primary table to the Foreign Key column of the child table in MySQL?

How to create a JDBC MySQL connection pool in Tomcat7 installed on Amazon EC2?

How to use alias in where clause in mysql

how to select data from mysql data base for current date and tommorrows date

How to load file into mysql DB on a shared hosting platform?

How to select on MySQL varbinary column using PHP Doctrine 2 ORM

How to add a node to xml value in MySql

How to get list of dates between two dates in mysql select query [duplicate]

How can i put pieces of text into a grid-like layout without using tables?

how to count the num of records in a group in mysql

How to use PHP Session to View MYsql Database Tables?

How to duplicate MySQL fields?

How to pass MySQL database connection data in namevaluepairs from Android to PHP

How to figure out if mysql index fits entirely in memory