CODEWITHSUNDEEP
phpmysqlinner-join
cybertextron
cybertextron

Reputation: 10981

creating a mysql table from a inner 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:

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
bases.database_id);
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?

Thanks

Upvotes: 2

Views: 2489

Answers (4)

Drewness
Drewness

Reputation: 5072

Instead of using SELECT * ... try SELECT database_id ...

MySQL does not like joining tables that have the same column name.

Upvotes: 0

Kyra
Kyra

Reputation: 5427

Don't use * instead name each column and use aliases. For instance instead of sitematrix_database.database_id you can have alternativeName. Also you can pick and choose which columns you want this way as well.

Upvotes: 1

paulsm4
paulsm4

Reputation: 121881

In SQL Server, you can use "select into". This might be equivalent syntax for mySql:

Unfortunately, it's a two commands (not just one):

CREATE TABLE recipes_new LIKE production.recipes; INSERT recipes_new SELECT * FROM production.recipes;

Upvotes: 0

hannebaumsaway
hannebaumsaway

Reputation: 2744

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

Upvotes: 1

Related Questions