SQL: How to create two columns from same data source based on conditions?

Question

I just started SQL learning, and am doing some practice problems online. I came across this problem here (#11 on the page): http://sqlzoo.net/wiki/AdventureWorks_hard_questions

Here's the schema for the 3 tables we will be using for this problem:

CustomerAW(CustomerID, FirstName, MiddleName, LastName, CompanyName, EmailAddress)

CustomerAddress(CustomerID, AddressID, AddressType)

Address(AddressID, AddressLine1, AddressLine2, City, StateProvince, CountyRegion, PostalCode)

And the question is:

For every customer with a 'Main Office' in Dallas show AddressLine1 of the 'Main Office' and AddressLine1 of the 'Shipping' address - if there is no shipping address leave it blank. Use one row per customer.

Below is my code to do so:

SELECT CustomerID, AddressType,  AddressLine1
FROM CustomerAW
JOIN CustomerAddress
USING (CustomerID)
JOIN Address
USING (AddressID)
WHERE AddressType IN 'Main Office'
AND  City = 'Dallas'
;

Below is the output I get:

Result:
CustomerID  AddressType AddressLine1
112         Main Office P.O. Box 6256916
130         Main Office Po Box 8259024
165         Main Office 2500 North Stemmons Freeway
201         Main Office Po Box 8035996
256         Main Office 99828 Routh Street, Suite 825.

But I don't know how to show the shipping address on a separate column. so that the headers become "CustomerID, AddressType, MainOfficeAddress, ShippingAddress". As stated in the problem, if there's no shipping address, leave it blank.

update

How to do this without using "view"? the website I'm testing on does not let me create views...

what I have tried

Well, I've tried different ways to achieve this, one of them is to write subqueries in the SELECT statement:

SELECT c.CustomerID,  
(
SELECT a.AddressLine1 FROM Address a
JOIN CustomerAddress ca
USING (AddressID)
WHERE ca.AddressType = 'Main Office'
) AS MainOfficeAddress
FROM CustomerAW c
;

But then I'll get the error saying "Subquery returns more than 1 row".

I've also tried something like below (none of them work actually):

SELECT c.CustomerID,  b.AddressLine1, e.AddressLine1
FROM CustomerAW c
JOIN
(
SELECT * FROM CustomerAddress ca
JOIN Address a
USING (AddressID)
WHERE ca.AddressType IN 'Main Office'
) AS b
USING (CustomerID)
JOIN
(
SELECT * FROM CustomerAddress ca
JOIN Address a
USING (AddressID)
WHERE ca.AddressType IN 'Shipping'
) AS e
USING (CustomerID)
;

And:

SELECT c.CustomerID, AddressType,  AddressLine1 AS MainOfficeAddress, 
(
    SELECT a.AddressLine1 FROM Address a
    JOIN CustomerAddress ca USING (AddressID)
    WHERE ca.CustomerID IN c.CustomerID
    AND ca.AddressType = 'Shipping'
) AS ShippingAddress
FROM CustomerAW c
JOIN CustomerAddress ca
USING (CustomerID)
JOIN Address
USING (AddressID)
WHERE ca.AddressType IN ('Main Office')
AND City = 'Dallas'
;

Any help is greatly appreciated! Hope I'm not asking a stupid question.

Tanuj Yadav · Accepted Answer · 2016-11-09 19:59:11Z

1

You should use 'inner join'. Write the same code as above, creating a view for simplicity, i mean

create view ee as
(
    SELECT CustomerID, AddressType,  AddressLine1
    FROM CustomerAW
    JOIN CustomerAddress
    USING (CustomerID)
    JOIN Address
    USING (AddressID)
    WHERE AddressType IN 'Main Office'
    AND  City = 'Dallas'
 )   ;

then write..

    create view rr as
    (
    SELECT CustomerID, AddressType,  AddressLine1
    FROM CustomerAW
    JOIN CustomerAddress
    USING (CustomerID)
    JOIN Address
    USING (AddressID)
    WHERE AddressType IN 'Shipping Address'
    );

then take the inner join of the views ee and rr

select CustomerID, AddressType, MainOfficeAddress, ShippingAddress
from ee
inner join
rr
on ee.customerID=rr.CustomerID

hope this helped.... ;)

answered Nov 9, 2016 at 19:59

Tanuj Yadav

1,28714 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

alwaysaskingquestions Over a year ago

Hi Tanuj, thank you so much for answering! May I ask how can you achieve this witout using "view"? Also... first time hearing using "view", can you expand a bit on what view is? I thought view was more for you to "view" a table, and not be used in queries...

Tanuj Yadav Over a year ago

a view is a virtual table, it refers to a real table. it stores some data. once a view is declared, it can be used just like a table.

alwaysaskingquestions Over a year ago

I see. so instaed of creating a new table, or using subqueries, you are using a view here instead. and views are only stored temporarily in memory, and won't be available next time when you try to pull it. am i understanding it correctly?

Tanuj Yadav Over a year ago

yes you are going correct . you can even update the table through a view. I mean, if you make changes to a view, those changes will be reflected in the table also, but only if the view refers to only a single table not multiple tables. please up vote the answer if it helped.

alwaysaskingquestions Over a year ago

Hi Tanuj, so you are saying the table values can actually be permanently modified via view? Also, I keep getting the error "CREATE VIEW command denied to user 'scott'@'localhost' for table 'ee'". Definitely upvoting your anwer. Thank you sooo much!!

|

Collectives™ on Stack Overflow

SQL: How to create two columns from same data source based on conditions?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related