0

I am have multiple mySQL tables that each have the same primary key but different columns. For instance, one table has a bunch of information about engine type and another table has information about transmissions. The VIN is used as the primary key in both tables.

I need to combine these tables into one larger table (the "master table) where the VIN is the primary key followed by all the columns from every table. I am doing this so that I can export a "master table" that contains all the vehicle information, as one table.

I am trying to do this without a bunch of SELECT (col1,col2,col3,....) in order to make it simple and save time, if that's possible.

Thanks!

Ben
  • 3
  • 1
  • You have to list the column names explicitly, because the `VIN` table is repeated in all the tables and you don't want multiple copies of this in the master table. – Barmar Apr 21 '15 at 21:00

3 Answers3

0

You have to list all the columns explicitly.

INSERT INTO masterTable (vin, col1, col2, col3, col4, col5, col6, col7, ...)
SELECT t1.vin, t1.col1, t1.col2, t1.col3, t2.col4, t2.col5, t3.col6, t3.col7, ...
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.vin = t2.vin
JOIN Table3 AS t3 ON t1.vin = t3.vin
...
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I was hoping to avoid this just cause it's so cumbersome, but if there isn't a work around this is perfect. – Ben Apr 21 '15 at 22:08
0

I would suggest to enumerate all items, but if you want to exclude VIN from other tables and have it only once you can probably check this solution Select all columns except one in MySQL?

Community
  • 1
  • 1
Stan
  • 1,410
  • 10
  • 14
0

If i understand you correctly you need just simple INNER JOIN sintax... try something like:

SELECT table1.colName, table1.colName2, table1.colNameN, table2.colName, table2.colName2, table2.colNameN
FROM table1
INNER JOIN table2
ON table1.VIN = table2.VIN
Aleksandar Miladinovic
  • 1,017
  • 2
  • 8
  • 10