How to solve ERROR 1060: Duplicate column name using Views -> Create View

Question

I am trying to create a view from joining some tables (in MySQL) from Sakila database (https://dev.mysql.com/doc/sakila/en/), namely I want to join payment, staff and customer and display the customer's name, the staff's name, the payment id and amount. I right clicked on Views and then Create Table. I think this error exists because of the fact that in the staff table and in the customer table there are both columns named first_name and last_name. How could I solve this problem?

My code:

CREATE VIEW `payment` AS
SELECT payment.payment_id, customer.first_name, customer.last_name, 
staff.first_name, staff.last_name, payment.amount
FROM payment INNER JOIN customer ON payment.customer_ID = customer.customer_ID
INNER JOIN staff ON payment.staff_ID = staff.staff_ID

The error message: ERROR 1060: Duplicate column name 'first_name'

If I try it with aliases, as some of the answerers suggested, I get the error ERROR 1347: 'sakila.payment' is not VIEW.

The exact same code works like a charm in SQL File, when I run it, it creates the table I need. What is the difference between these two methods? Which one should I use? And why didn't it work with the Views -> Create View option?

Thank you in advance.

Solution 1

Use aliases to assign names:

CREATE VIEW v_payment AS
    SELECT p.payment_id, c.first_name as customer_first_name, c.last_name as customer_last_name,
           s.first_name as staff_first_name, s.last_name as staff_last_name,
           p.amount
    FROM payment p INNER JOIN
         customer c
         ON p.customer_ID = c.customer_ID INNER JOIN
         staff s
         ON p.staff_ID = s.staff_ID;

first_name and last_name appear twice in your select list. The above clarifies whether the name is for a customer or staff.

Solution 2

The restriction is that the column names must be unique.

The SELECT query is probably valid, but it does return columns that have the same name. Two columns with the name first_name, and two columns named last_name.

The error crops up when we use the query as a view (either an inline view, or a stored view.)

The workaround is to rename the columns by providing a column alias, so that no two columns in the resultset have the same name. For example:

SELECT payment.payment_id
     , customer.first_name   AS customer_first_name
     , customer.last_name    AS customer_last_name
     , staff.first_name      AS staff_first_name
     , staff.last_name       AS staff_last_name
     , payment.amount
  FROM payment 
  JOIN customer
    ON ...
  JOIN staff
    ON ...