sql - SQLZoo Hard Questions: Get results that share an ID on the same row -

http://sqlzoo.net/wiki/adventureworks_hard_questions

question 11 states:

for every customer 'main office' in dallas show addressline1 of 'main office' , addressline1 of 'shipping' address - if there no shipping address leave blank. use 1 row per customer.

relevant tables/ids are:

customer             customeraddress              address customer id          customerid                   addressid                      addressid                    addressline1                      addresstype                  city 

my current code

select ca.customerid,  case when ca.addresstype = 'main office' a.addressline1 else "" end, . case when ca.addresstype = 'shipping' a2.addressline1 else "" end  address join customeraddress ca  on a.addressid = ca.addressid  join address a2  on a.addressid = a2.addressid a.city = 'dallas' 

there's 5 total main offices in dallas, , 1 has shipping address.

when tried "group customerid" returns 1 of addresses, if i've searched both of them in query above.

how both addresses return on same row?

first in cte filter customers dallas. left joining find shipping addresses:

;with cte as(select ca.customerid, a.addressline1              customeraddress ca               join address on ca.addressid = a.addressid              ca.addresstype = 'main office' , a.city = 'dallas')  select c.customerid,        c.addressline1 mainaddress,        a.addressline1 shippingaddress cte c left join customeraddress ca on c.customerid = ca.customerid ,                                  ca.addresstype = 'shipping' left join address on ca.addressid = a.addressid