How to create view in query having JOINS?

Question

Hello I am not able to create view for following query.

create view  my_View as 
select *  from product p 
LEFT JOIN product_description pd ON (p.product_id=pd.product_id)

I have two tables
TABLE1 :- Product with column as :- product_id, column2,column3
TABLE2 :- Product_Description with column as:- product_id , column4,column5. Here product_id is foreign key for Product table.

Following is the error I am getting

Error Code: 1060. Duplicate column name 'product_id'

I am using mysql workbench to run my queries. Looking at the error, I understand that I need to assign alias. But I am required to join more than 5-8 tables thats the reason I am looking for better solution.

Your `SELECT *` is returning both columns named `product_id` - instead of using `SELECT *` list your columns such as `select product_id, column2, column3`, etc. You shouldn't need to return `product_id` twice. — Taryn, Dec 29 '14 at 15:04
As mentioned I understood that approach. But I thought if there is some other way. — Ashish Agarwal, Dec 29 '14 at 15:14
@bluefeet If you would have gone through full description than you would have not echoed what I already wrote. — Ashish Agarwal, Dec 29 '14 at 15:16
I did read your full question, using `SELECT *` is a bad habit. — Taryn, Dec 29 '14 at 15:18
@Ashish You need to **type**. There is no magic solution for disambiguation of columns due to laziness. — Aaron Bertrand, Dec 29 '14 at 15:18
possible duplicate of [#1060 - Duplicate column name 'id'](http://stackoverflow.com/questions/4815627/1060-duplicate-column-name-id) — billinkc, Dec 29 '14 at 15:19

score 2 · Answer 1 · answered Dec 29 '14 at 15:06

The Error is coming due to same column name in both the tables, second thing is its never a good practice to fetch all records in views by using * until and unless you really want all records from all table that you are using in join condition.

What you can do is select records specific to your view, like

select p.*,pd.somefield  from product p 
LEFT JOIN product_description pd ON (p.product_id=pd.product_id)

In the above query your product_id is fetched from product table only and you can fetch only necessary fields from product_description table

score 1 · Accepted Answer · answered Dec 29 '14 at 15:05

1

The easiest solution in your case is to use using instead of on:

create view  my_View as 
    select * 
    from product p LEFT JOIN
         product_description pd 
         USING (product_id);

In general, though, you should list the columns explicitly in the select rather than using *.

answered Dec 29 '14 at 15:05

Gordon Linoff

1,242,037
58
646
786

1

Not my DV but I don't think that will resolve the error. – Taryn Dec 29 '14 at 15:08
@bluefeet . . . (1) It should. (2) It does in SQL Fiddle http://www.sqlfiddle.com/#!2/b53a0. Can you please remove the downvotes? – Gordon Linoff Dec 29 '14 at 15:13
2

As I said, it's not my DV so I can't remove it. – Taryn Dec 29 '14 at 15:15
IMHO this "solution" is extremely brittle and not a good suggestion at all. It relies on the assumption that the joining key is the only column name shared between the two tables today, and that that will remain true in the future as well. This breaks if both tables have a column called `ModifiedDate`, for example. – Aaron Bertrand Dec 29 '14 at 15:24
@AaronBertrand . . . The suggestion is to list all the columns (see what I write after the suggestion). The `using` is a way to get around the particular problem in the OP's original question (which only involved two tables). – Gordon Linoff Dec 29 '14 at 15:26
But `USING` only works around the key columns, not any others. While that may work given the limited information the OP has provided, and in this specific scenario only, it is still a dangerous thing to advocate in general. Remember that answers here are not just for *this OP*. – Aaron Bertrand Dec 29 '14 at 15:38

score 1 · Answer 3 · answered Dec 29 '14 at 15:06

SELECT * will select all columns from both tables in the join. But both tables have a column called product_id, so you'll get two columns in the view with the same name.

You need to list out all the columns, so you can give an alias to product_description.product_id to distinguish it:

SELECT p.product_id, p.column2, p.column3, pd.product_id AS pd_id, pd.column4, pd.column5
FROM product AS p
LEFT JOIN product_description AS pd ON p.product_id = pd.product_id

How to create view in query having JOINS?

3 Answers3