Create table from joined table in MySQL-CodePudding

I have no problem joining the tables, but when I go to create a new table using the joined tables, I get an error saying that I have duplicate columns.

My code:

SELECT *
FROM field
INNER JOIN race
ON field.raceID = race.raceID;

Updated code:

CREATE TABLE fieldrace AS
SELECT f.*, r.*
FROM field f
INNER JOIN race r
ON f.raceID = r.raceID;

CodePudding user response：

That's true of any select. If there are duplicated column names, you have to reference them somehow. For a .* query this would work:

SELECT f.*, r.*
FROM field f
INNER JOIN race r
ON f.raceID = r.raceID;

Individually you can also add aliases. Maybe you have an id column in both race and field tables.

SELECT f.id as field_id, r.id as race_id, ....
FROM field f
INNER JOIN race r
ON f.raceID = r.raceID;

CodePudding user response：

In the query

CREATE TABLE fieldrace AS
SELECT f.*, r.*
FROM field f
INNER JOIN race r
ON f.raceID = r.raceID;

SELECT part produces two columns with the same name in the output.

Two columns with the same name presence is not allowed in table's structure, and the whole query will fail.

General solution is to list each output column in the SELECT part separately with assigning them unique aliases.

If raceID column which is used for joining is the only column whose name interferes then you may use either USING clause instead of ON clause or NATURAL JOIN instead of INNER JOIN.

CREATE TABLE fieldrace AS
SELECT f.*, r.*
FROM field f
INNER JOIN race r USING (raceID);
-- or
CREATE TABLE fieldrace AS
SELECT f.*, r.*
FROM field f
NATURAL INNER JOIN race r;

In both cases the interfered columns will be collapsed into one column which will be placed to the top of created table structure.

Of course when raceID is not the only column whose name interferes then 1st of these queries will fail due to another column duplication whereas 2nd query will use all interfered columns for joining.

You may specify complete or partial structure of newly create table. In this case the amount and relative posession of the columns in the created table won't be changed (will match SELECT output) but all another properties of the columns (datatype, nullability, etc.) and additional objects (indices, constraints, etc.) listed in the structure will be applied. The columns which are absent in the output (including generated ones) will be added into the structure with default values as the most first ones, before the columns used in USING or during NATURAL JOIN even. DEMO.

CodePudding user response：

you can create "view" or name a subquery using "with" in both cases, you can access it from anywhere in your main query