List:General Discussion« Previous MessageNext Message »
From:Johan Höök Date:October 7 2005 8:25am
Subject:Re: Duplicate column error when creating view on 5.0.13
View as plain text  
Hi Terence,
I guess I was a bit unclear on my comment on the
SELECT *. Anyway you did what I meant, i.e. changed
one of your category_id's so that the set
of columnnames ( as implied by the SELECT * ) becomes
unique. What happens is that your SELECT * becomes
SELECT ticket_id,category_id,category_id,category_name
when MySQL deduces what to use for columnnames in the view.

/Johan

Terence wrote:
> 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