Mysql – When creating a view how to separate one column into two depending on a foreign key value

MySQL

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

Best Answer

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

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