Avoid "# 1060 - Duplicate Column Name" Error, but when using SELECT *

Question

I have 2 tables, advertisers and campaigns. Both have more fields that I need to list, and both have a primary key of "id". I would like to create a view without having to enter all the fields manually using *. I keep getting the "duplicate column" error. Is it possible to do this with * in general or just my only way to enter all column names and set aliases for identifiers?

  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'

Answer 1

MySQL ( ) using join on.

, , . , :

CREATE VIEW VIEW_CAMPAIGNS AS      *,      c join            a            (advertisers_id);

, , , *, .

, , information_schame.columns . - :

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

Answer 2

,

  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

.

• : , , *. (40) .

EDIT:

.