I'm supposed to create a view that joins the Customer table with the Address table and Address type table. Call it Customer_View. Have two sections for the Address; Physical and Mailing. Make sure that your column headings reflect the two sections. If they are both you can just put the details under the Physical.
These are my tables with columns
Customers Columns: Customer_Id Last_Name First_Name Middle_Name Phone Email
Address Columns: Address_Id Customer_Id Address_Type_Id Address_1 Address_2 City State Zip Country
address_type Columns: Address_Type_Id Description
I know how to join the tables but I don't know how to make it so the address column shows as two columns, one for physical and one for mailing, depending on if the address_type_id value is 1 & 3 or 2... does that make sense?
This is the code I have:
CREATE OR REPLACE VIEW Customer_View AS SELECT c.customer_id, CONCAT(First_Name, ' ', Last_Name) AS 'Full_Name', Address_1, Address_2, Phone, Email, City, State, Zip, Country, FROM address AS a JOIN customer AS c ON a.customer_id = c.customer_id JOIN address_type AS aty ON a.address_type_id = atp.address_type_id