Creating a mysql table from an internal join


I'm trying to create a mysql table from the inner join between two other tables. I'm dealing with a database someone creates which has the following tables:

  • sitematrix_sites
  • sitematrix_databases

They are related by another table (I don't know why don't use a foreign key) called sitematrix_sites_databases which has the following fields: site_id and database_id.

That's how the two tables relate. Now I'm trying to remove that to make my life easier, so I have:

mysql> CREATE TABLE result AS(select * from sitematrix_databases INNER JOIN site
matrix_site_databases ON sitematrix_site_databases.database_id = sitematrix_data
ERROR 1060 (42S21): Duplicate column name 'database_id'

However, I'm getting that error. Does someone know how can I merge the two tables without repeating the database_id field?


Remove the * in your SELECT statement and actually list out the columns you want in your new table. For columns that appear in both original tables, name the table as well (e.g. sitematrix_databases.database_id).