Terence <[EMAIL PROTECTED]> 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
