List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 7 2005 12:57pm
Subject:Re: Duplicate column error when creating view on 5.0.13
View as plain text  
Terence <terence@stripped> wrote on 10/07/2005 03:49:46 AM:

> Hi All,
> 
> I get a duplicate column error on 5.0.13 when creating a view. Am I 
> doing something wrong here or do I submit a bug report? This should be 
> allowed as the col names
> are not duplicated.
> 
> Reproduce:
> 
> create table `ticket_master` (
> `ticket_id` int (5) NOT NULL AUTO_INCREMENT ,
> `category_id` int (5) NULL,
> PRIMARY KEY ( `ticket_id` ));
> 
> create table `category_master` (
> `category_id` int (5) NOT NULL AUTO_INCREMENT ,
> `category_name` varchar (20) NULL,
> PRIMARY KEY ( `ticket_id` ));
> 
> 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'
> 
> Nothing wrong with the query as you can see
> 
> SELECT * FROM ticket_master tm, category_master cm
> WHERE tm.category_id = cm.category_id;
> +-----------+-------------+-------------+---------------+
> | ticket_id | category_id | category_id | category_name |
> +-----------+-------------+-------------+---------------+
> |         1 |           1 |           1 | test          |
> +-----------+-------------+-------------+---------------+
> 1 row in set
> 
> Thanks
> 
I think what everyone has been trying to explain and what you missed by 
looking at your own sample output is that you DO have duplicate names in 
your view. One column from table ticket_master is called `category_id`, 
one column from category_master is also called `category_id`.  Because you 
wanted ALL of the columns from BOTH tables then both columns were supposed 
to be added to the view.

Your hack of changing the column definition on the base table works but is 
the wrong approach. The correct approach is to define the view in such a 
way as to avoid creating two columns of the same name. Here is one way to 
do it:

CREATE VIEW `v_tickets` AS (
SELECT ticket_id, tm.category_id, category_name
FROM ticket_master tm
INNER JOIN category_master cm
        ON cm.category_id = tm.category_ID
);

ANOTHER WAY:

CREATE VIEW `v_tickets` AS (
SELECT ticket_id, tm.category_id, category_name
FROM ticket_master tm
INNER JOIN category_master cm
        ON cm.category_id = tm.category_ID
);

ANOTHER WAY:

CREATE VIEW `v_tickets` AS (
SELECT ticket_id, tm.category_id, cm.category_id as cmCat_ID, 
category_name
FROM ticket_master tm
INNER JOIN category_master cm
        ON cm.category_id = tm.category_ID
);

This last two methods have the advantage of being able to say "give me a 
view where I can see what categories I have and how many tickets are 
assigned to each by merely changing the INNER JOIN to a RIGHT JOIN like 
this:

CREATE VIEW `v_tickets` AS (
SELECT ticket_id, tm.category_id, category_name
FROM ticket_master tm
RIGHT JOIN category_master cm
        ON cm.category_id = tm.category_ID
);

In every case, I made sure that the VIEW did not get more than one column 
(from any table participating in it) in it's output that had the same name 
as any other. In the third version I applied an alias to the duplicate 
column (from one of the tables) to make it unique. In the others, I just 
didn't ask for the offending duplicate to appear at all.

Does this make better sense?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
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