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:

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

1 Answer 1


you need to use CASE construction in the query SELECT list:

      WHEN aty.address_type_id IN (1,3) THEN a.Address1 ELSE NULL END as mail_address,
      WHEN aty.address_type_id =2 THEN a.Address1 ELSE NULL END as physical_address
FROM ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.