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'

Solution

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

Comments

[Rid Iculous]

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

[Awena]

Thanks a million! 'CREATE VIEW VIEW_CAMPAIGNS AS SELECT *, FROM campaigns c join advertisers a using (advertisers_id);' solved my issue of duplicate cols