Vertically Merge Multiple Tables in MySQL by Joint Primary Key

Question

Hello, I'll attempt to make my question as clear as possible. I'm fairly unexperienced with SQL, only know the really basic queries. In order to have a better idea I'd been reading the MySQL manual for the past few days, but I couldn't really find a concrete solution and my needs are quite specific.

I've got 3 MySQL MyISAM tables: table1, table2 and table3. Each table has an ID column (ID, ID2, ID3 respectively), and different data columns. For example table1 has [ID, Name, Birthday, Status, ...] columns, table2 has [ID2, Country, Zip, ...], table3 has [ID3, Source, Phone, ...] you get the idea.

The ID, ID2, ID3 columns are common to all three tables... if there's an ID value in table1 it will also appear in table2 and table3. The number of rows in these tables is identical, about 10m rows in each table.

What I'd like to do is create a new table that contains (most of) the columns of all three tables and merge them into it. The dates, for instance, must be converted because right now they're in VARCHAR YYYYMMDD format. Reading the MySQL manual I figured STR_TO_DATE() would do the job, but I don't know how to write the query itself in the first place so I have no idea how to integrate the date conversion.

So basically, after I create the new table (which I do know how to do), how can I merge the three tables into it, integrating into the query the date conversion?

Answer 1

Looks like you want an INSERT SELECT query along the lines of:

INSERT INTO [new table] 
SELECT [values] 
FROM table1 
INNER JOIN table2 on table1.ID = table2.ID2 
INNER JOIN table3 ON table1.ID = table3.ID3;

Where you fill in [new table] as the name of the new table and [values] as the values you want in the new table.

Here are the relevant parts of the manual for more details.

• INSERT...SELECT syntax - for details of the INSERT SELECT statement
• JOIN syntax - for details on JOINing tables in queries

Answer 2

Thanks for your help, I will test it. The only thing that's unclear to me is how I can convert the dates within the query. As far as I understand the query should be something like that:

INSERT INTO [new table] 
SELECT table1.ID, table1.Name, table1.Birthday, table2.Country, table3.Phone
FROM table1 
INNER JOIN table2 ON table1.ID = table2.ID2 
INNER JOIN table3 ON table1.ID = table3.ID3;

...but how can I convert the dates within it? Or for that matter, apply any function to a field before it's inserted? For instance how can I convert the Birthday field before inserting it using STR_TO_DATE()? Where do I put it?

STR_TO_DATE(table1.Birthday, '%Y%m%d')

Thanks in advance.

[Err I figured just replace "table1.Birthday" with "STR_TO_DATE(table1.Birthday, ...)"? Is that correct?]