从内部联接创建mysql表 [英] creating a mysql table from a inner join
问题描述
我正在尝试从其他两个表之间的内部联接创建一个mysql表.我正在处理有人创建的具有以下表格的数据库:
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
sitematrix_sitessitematrix_databases
它们与另一个名为sitematrix_sites_databases的表(我不知道为什么不使用外键)相关，该表具有以下字段:
site_id和database_id.
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'
但是，我遇到了这个错误.有人知道如何在不重复database_id字段的情况下合并两个表吗?
However, I'm getting that error. Does someone know how can I merge the two tables without repeating the database_id field?
谢谢
推荐答案
删除SELECT语句中的*，并实际上列出要在新表中使用的列.对于同时出现在两个原始表中的列，请同时命名该表(例如sitematrix_databases.database_id).
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).
这篇关于从内部联接创建mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
