MySQL - How to create a new table that is a join o

Question

I have two existing tables, with different fields, except for Primary ID (a varchar, not an int). I want to create a third table which is essentially a merge of these two, such that for a given Primary Key I have all fields in one table.

What's the bext way of doing this?

Many thanks

Answer 1

CREATE TABLE result AS 
  (SELECT first.*, 
          second.f1, 
          second.f2, 
          second.f3 
   FROM   first 
          INNER JOIN second 
                  ON first.id = second.id);

To get a view, do the same except replace "TABLE" with "VIEW". If you go with the table rather than the view, make sure to add a primary key as that will not be added by default.

Answer 2

Why are you creating a new table? Why don't you just execute a query whenever you need the data? If you're just joining two tables on their primary key, then the majority of your data access time is going to be spent marshalling the data back to your application. You're not going to be saving much time pre-joining the tables, and you'll be eating a lot of space. Plus, you're taking aim at your big toe, just waiting for the first time you update your source tables and forget to run your update script to copy the changes to your joined table. Duplicate data is evil, but sometimes it's necessary. This doesn't sound like one of those times.

Answer 3

For MS SQL use this

SELECT * INTO result 
FROM  table1
INNER JOIN table2
ON table1.id = table2.id

Answer 4

If you are sure you have one and exactly one row in both tables for a given primary ID, then this should work:

SELECT
    tablea.field1, tablea.field2, tablea.field3, ... tablea.fieldn, <---- field list
    tableb.field1, tableb.field2, tableb.field3, ... tableb.fieldm  <---- field list
FROM
    tablea, tableb
WHERE
    tablea.primaryID = tableb.primaryID

You might want to omit tablea's and tableb's primary ID field from the field list if you do not actually need them (in this query both will contain the same value due to the tablea.primaryID = tableb.primaryID condition).

The syntax is relatively similar for a VIEW as well.