Question

Create a view named customer_addresses that shows the shipping and billing addresses for each customer. This view should return these columns from the Customers table: customer_id, email_address, last_name and first_name. This view should return these columns from the Addresses table: bill_line1, bill_line2, bill_city, bill_state, bill_zip, ship_line1, ship_line2, ship_city, ship_state, and ship_zip.

I have this

CREATE VIEW customer_addresses AS

SELECT customer_id, email_address, last_name, first_name,
bill_line1, bill_line2, bill_city, bill_state, bill_zip,
ship_line1, ship_line2, ship_city, ship_state, ship_zip

FROM Customers, Addresses

WHERE Customers.customer_id = Addresses.customer_id

ORDER BY last_name, first_name;

Error Code: 1052. Column 'customer_id' in field list is ambiguous

Answer 1

Changes to be made:

i) Error Code: 1052 means SQL cannot read or support 'customer_id'. It means SQL don't know from where to choose customer_id column either from Customers table or Addresses table because it is available in both tables. (The word 'ambiguos' means 'uncertain')

To solve this problem,you need to prefix your customer_id with the table you want to choose it from.

ii) Use JOIN to retrieve fields from multiple tables.

The syntax for INNER JOIN is,

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Now the usable query becomes,

CREATE VIEW customer_addresses AS

SELECT Customers.customer_id, Addresses.customer_id, email_address, last_name, first_name,bill_line1, bill_line2, bill_city, bill_state, bill_zip,
ship_line1, ship_line2, ship_city, ship_state, ship_zip

FROM Customers

INNER JOIN Addresses ON Addresses.customer_id = Customers.customer_id

ORDER BY last_name, first_name;