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.

Answer 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 *.