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