MySQL - How to create a new table that is a join on primary key of two existing tables
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.
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.