Duplicate column name in SQL

Question

I've wanted to create a view in my sql but I've been getting this error:

Duplicate column name 'product_id'

Here's my query:

CREATE VIEW product_view AS

SELECT
    *
FROM
    products
    JOIN storeitems on products.product_id = storeitems.product_id;

I believe creating an alias would solve the problem but then I don't know how to do it. I hope you could help me with this. Thanks!

Answer 1

You get the error because both tables in your query has a column named product_name and selected column names must be unique.

You make your aliases like this:

CREATE VIEW product_view AS SELECT p.product_name, si.product_name StoreItemsProductName -- continue with what you want to select...
FROM products p JOIN storeitems si
on p.product_id=si.product_id;