0

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
1

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 ...
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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