CODEWITHSUNDEEP
mysqlsqlviewkey
Rid Iculous
Rid Iculous

Reputation: 3962

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

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'

Upvotes: 5

Views: 18636

Answers (2)

echo_Me
echo_Me

Reputation: 37233

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

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

Upvotes: 8

Related Questions