List:General Discussion« Previous MessageNext Message »
From:Terence Date:October 7 2005 8:11am
Subject:Re: Duplicate column error when creating view on 5.0.13
View as plain text  
Johan Höök wrote:
> Hi Terence,
> 
> I think your problem lies in your SELECT *
> If you look at the columnheaders below you get category_id twice.
> I guess you have to specify your columns with aliases.
> 
> /Johan
> 

Hi Johan,

Nope, if I change the column name to category_id1 then it's okay. See 
this: (apologies for the previous typo in the reproducible script)

create table `ticket_master` (
`ticket_id` int (5) NOT NULL AUTO_INCREMENT ,
`category_id` int (5) NULL,
PRIMARY KEY ( `ticket_id` ));

Query OK, 0 rows affected

create table `category_master` (
`category_id` int (5) NOT NULL AUTO_INCREMENT ,
`category_name` varchar (20) NULL,
PRIMARY KEY ( `category_id` ));

Query OK, 0 rows affected

CREATE VIEW `v_tickets` AS
(
SELECT * FROM ticket_master tm, category_master cm
WHERE tm.category_id = cm.category_id
);

ERROR 1060 : Duplicate column name 'category_id'

alter table `category_master` ,change `category_id` `category_id1` int 
(5)   NOT NULL AUTO_INCREMENT ;

CREATE VIEW `v_tickets` AS
(
SELECT * FROM ticket_master tm, category_master cm
WHERE tm.category_id = cm.category_id1
);

Query OK, 0 rows affected

Thread
Duplicate column error when creating view on 5.0.13Terence7 Oct
  • Re: Duplicate column error when creating view on 5.0.13Johan Höök7 Oct
    • Re: Duplicate column error when creating view on 5.0.13Terence7 Oct
      • Re: Duplicate column error when creating view on 5.0.13Johan Höök7 Oct
  • Re: Duplicate column error when creating view on 5.0.13Martijn Tonies7 Oct
  • Re: Duplicate column error when creating view on 5.0.13SGreen7 Oct
    • Re: Duplicate column error when creating view on 5.0.13Terence10 Oct