Search code examples
mysqljoinmergecreate-table

MySQL - How to create a new table that is a join on primary key of two existing tables

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.

How can this be done?

Solution

  • 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.