Avoid "#1060 - Duplicate column name" Error, but still using SELECT *

Question

I have 2 tables, advertisers and campaigns. Both have more fields I care to list and both have a primary key "id". I'd like to create a view without having to enter all fields manually using * instead. I keep getting the "duplicate column" error. Is it possible to do this via * at all or is my only option to enter all column names and setting aliases for the IDs?

  CREATE VIEW VIEW_CAMPAIGNS AS 

  SELECT *, 
      advertisers.id as adv_id,
      campaigns.id as camp_id

  FROM  campaigns, advertisers
  WHERE advertisers.id = advertiser_id

Still returns #1060 - Duplicate column name 'id'

Yes, by saying *, you mean all columns from both tables, the fact that you're also adding 2 more columns explicitly and giving them a new alias doesn't change the fact that you've already selected all columns from both tables. — Brian DeMilia, Feb 18 '14 at 23:18
Yes. But I'm also saying don't use * period. Best practice is to always name (and fully identify) all columns. — Strawberry, Feb 18 '14 at 23:21

score 8 · Accepted Answer · answered Feb 19 '14 at 03:32

You can do this in MySQL (and other standard databases) by using the using form of the join instead of the onclause.

Unfortunately, you can do that here because the join keys have different ids in the two tables. If they had the same name, you would just do:

CREATE VIEW VIEW_CAMPAIGNS AS SELECT *, FROM campaigns c join advertisers a using (advertisers_id);

The nearest you can do is to choose the bigger table, use * for that and then list all the columns for the other table.

Or, better yet, just use the information_schame.columns table to generate the column names. Something like:

select (case when column_name = 'id' and table_name = 'campaigns' then `c.campaign_id`
             when column_name = 'id' and table_name = 'advertisers' then 'a.advertiser_id'
             when table_name = 'campaigns' then concat('c.', column_name)
             when table_name = 'advertisers' then concat('a.', column_name)
        end) as column_name
from information_schema.columns
where table_name in ('campaigns, 'advertisers')

Thanks a million! 'CREATE VIEW VIEW_CAMPAIGNS AS SELECT *, FROM campaigns c join advertisers a using (advertisers_id);' solved my issue of duplicate cols — Awena, Apr 29 '15 at 17:03

score 0 · Answer 2 · edited May 23 '17 at 12:32

0

you can do it that why

  CREATE VIEW VIEW_CAMPAIGNS AS 

  SELECT u.id AS u_id
       ....
  , u2.id AS u2_id

  FROM  campaigns u
  INNER JOIN advertisers u2
  ON u.id = u2.advertiser_id

then its automatically columns are differentited .

for performance : better use columns which you need only and not using * . to not select all columns (40) every time.

EDIT:

Aliases can be used for individual columns.

You'll have to alias each column instead

check this answer

edited May 23 '17 at 12:32

Community

1
1

answered Feb 18 '14 at 23:26

echo_Me

37,078
5
58
78

I don't understand this answer. What do the (...) mean? If I need to add the field-names/aliases there the whole undertaking is pointless. The idea was to use *. Everybody basically says it can't be done. (?) – Rid Iculous Feb 20 '14 at 02:37

Avoid "#1060 - Duplicate column name" Error, but still using SELECT *

2 Answers2