Duplicate column names in SQL query

Question

How do I avoid a dup column name error in MySQL when creating a VIEW on two tables that both have the same column name as shown here

CREATE VIEW db.VI_RegionCity AS SELECT
    Region.Name,
    City.Name
FROM
    db.Region Region,
    db.City City
WHERE
    Region.RegionCode = City.RegionCode



ERROR: Duplicate column name 'Name'

score 4 · Accepted Answer · answered Jun 08 '11 at 06:02

4

Using alias for column name

CREATE VIEW db.VI_RegionCity AS SELECT
    Region.Name as Region_name,
    City.Name as City_name
...//rest of the query

answered Jun 08 '11 at 06:02

Shakti Singh

84,385
21
134
153

5

Is there a way to use "select * ..." and JUST avoiding the duplicates? I have a table with 20 odd fields and only the IDs are in both tables. I don't want to type out all field names. – Rid Iculous Feb 18 '14 at 23:02

score 1 · Answer 2 · answered Jun 08 '11 at 06:03

1

CREATE VIEW db.VI_RegionCity AS SELECT
    Region.Name AS RegionName,
    City.Name AS CityName
FROM
    db.Region Region,
    db.City City
WHERE
    Region.RegionCode = City.RegionCode

answered Jun 08 '11 at 06:03

Stefan H

6,635
4
24
35

Duplicate column names in SQL query

2 Answers2

Linked