Tricky SQL question

Greetings, experts!

It's late on a Friday afternoon, so I apologize if this question is mind-numbingly easy, but I'm not seeing a solution. (I think I need more coffee.) :-)

I have three tables: Customer, Address, and CustomerAddress (which is the intersection of Customer and Address).

The data looks like this in those tables:

select *             
  from Customer

CUSTOMERID   CUSTOMERNAME
         1   bob         
         2   kate        
         3   rowan       

select *
  from Address

ADDRESSID   STREET1                    STREET2                    CITY             STATE  ZIPCODE  
        1   123 BobAndKates apartment  13th Floor                 MyCity            MA    123456789
        2   321 Bobs job               -                          MyOtherCity       MA    123456780
        3   567 Rowans job             suite 100                  boston            MA    02115    

select *
  from CustomerAddress

CUSTOMERID       ADDRESSID   ADDRESSTYPE
         1               1      home    
         2               1      home    
         1               2      work    
         3               3      work    

Open in new window


I can successfully get the matches:

select c.customerName,
       ca.addressType,
       a.street1,
       a.street2,
       a.city,
       a.state,
       a.zipcode
  from Customer c
  join CustomerAddress ca
    on ca.customerId = c.customerId
  join Address a
    on a.addressId = ca.addressId

CUSTOMERNAME  ADDRESSTYPE  STREET1                    STREET2                    CITY             STATE  ZIPCODE  
 bob             home      123 BobAndKates apartment  13th Floor                 MyCity            MA    123456789
 bob             work      321 Bobs job               -                          MyOtherCity       MA    123456780
 kate            home      123 BobAndKates apartment  13th Floor                 MyCity            MA    123456789
 rowan           work      567 Rowans job             suite 100                  boston            MA    02115    

Open in new window


Now, the requirement comes to only return ONE address per person. Choose the work-address, if it's available. If the work-address is not available, choose the home-address.

select c.customerName,                                           
       coalesce(wca.addressType, hca.addresstype) as addressType,
       coalesce(wa.street1, ha.street1) as street1,              
       coalesce(wa.street2, ha.street2) as street2,              
       coalesce(wa.city, ha.city) as city,                       
       coalesce(wa.state, ha.state) as state,                    
       coalesce(wa.zipcode, ha.zipcode) as zipcode               
  from Customer c                                                
  left outer join CustomerAddress wca                            
    on wca.customerId = c.customerId                             
   and wca.addressType = 'work'                                  
  left outer join CustomerAddress hca                            
    on hca.customerId = c.customerId                             
   and hca.addressType = 'home'                                  
  left outer join Address wa                                     
    on wa.addressId = wca.addressId                              
  left outer join Address ha                                     
    on ha.addressId = hca.addressId 

CUSTOMERNAME  ADDRESSTYPE  STREET1                    STREET2                    CITY             STATE  ZIPCODE   
 bob             work      321 Bobs job               13th Floor                 MyOtherCity       MA    123456780 
 kate            home      123 BobAndKates apartment  13th Floor                 MyCity            MA    123456789 
 rowan           work      567 Rowans job             suite 100                  boston            MA    02115     

Open in new window


Notice that it took Bob's STREET2 value from his home-address instead of the null in the work-address, despite using the rest of the work-adress (street1, city, state, zipcode). Of course, that happens because we're COALESCE'ing together the work-address.street1 and the home-address-street2. So, if the work street2 is null, and the home street2 isn't null, it'll take the non-null one.


With the data that I've been given, how can I re-write the query to return ALL of the work-address or ALL of the home address, but not a partial part of each?

Thanks in advance!
DaveSlash
LVL 18
Dave FordSoftware Developer / Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
I think you should do something like this. I did not have tested, but the logic is that whatever is determined by the coalesce of the type, it will be the address that will appear on your result:
select c.customerName,                                           
       coalesce(wca.addressType, hca.addresstype) as addressType,
       decode(coalesce(wca.addressType, hca.addresstype),'work',wa.street1, ha.street1) as street1,              
       decode(coalesce(wca.addressType, hca.addresstype),'work',wa.street2, ha.street2) as street2,              
       decode(coalesce(wca.addressType, hca.addresstype),'work',wa.city, ha.city) as city,                       
       decode(coalesce(wca.addressType, hca.addresstype),'work',wa.state, ha.state) as state,                    
       decode(coalesce(wca.addressType, hca.addresstype),'work',wa.zipcode, ha.zipcode) as zipcode               
  from Customer c                                                
  left outer join CustomerAddress wca                            
    on wca.customerId = c.customerId                             
   and wca.addressType = 'work'                                  
  left outer join CustomerAddress hca                            
    on hca.customerId = c.customerId                             
   and hca.addressType = 'home'                                  
  left outer join Address wa                                     
    on wa.addressId = wca.addressId                              
  left outer join Address ha                                     
    on ha.addressId = hca.addressId 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Please post your expected results give the sample data.
slightwv (䄆 Netminder) Commented:
If I had to guess:  The SQL posted by Walter seems to provide what I think you want.

The problem is it seems to query the address table twice.

This provides the same results and only accesses each table only once.

You will need to testthe two to see which one is best:
select customerName,
       addressType,
       street1,
       street2,
       city,
       state,
       zipcode
from (
select c.customerName,
       ca.addressType,
       a.street1,
       a.street2,
       a.city,
       a.state,
       a.zipcode,
row_number() over(partition by c.customerid order by case ca.addresstype when 'work' then 1 when 'home' then 2 end) rn
  from Customer c
  join CustomerAddress ca
    on ca.customerId = c.customerId
  join Address a
    on a.addressId = ca.addressId
)
where rn=1
/

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mike EghtebasDatabase and Application DeveloperCommented:
I made some temp tables and ran the following derived table query. It produces what you are asking for.

CUSTOMERID   CUSTOMERNAME    ADDRESSID    STREET1             STREET2              CITY     STATE    ZIPCODE    ADDRESSTYPE
1	         bob	      2	        123 Bobs job	          -	        MyOtherCity MA	   123456780	work
2	         kate	      1	        123 BobAndKates    apartment 13th Floor	MyCity	    MA	   123456789	home
3	         rowan	      3	        567 Rowans job	   suite 100r           boston	    MA	   02115	work

Open in new window


Here is the query: (please remove the extra fields I have included and also remove # from the table names)
SELECT D.CUSTOMERID, D.CUSTOMERNAME, D.ADDRESSID, D.STREET1, D.STREET2, D.CITY, D.[STATE], D.ZIPCODE, D.ADDRESSTYPE
FROM (select       
ROW_NUMBER() OVER(PARTITION BY c.CUSTOMERID
ORDER BY ADDRESSTYPE Desc) AS rownum,                                
       c.CUSTOMERID
       , c.CUSTOMERNAME
	   , a.ADDRESSID
	   , a.STREET1 
	   , a.STREET2
	   , a.CITY
	   , a.[STATE]
	   , a.ZIPCODE
	   , ca.ADDRESSTYPE                          
  from #Customer c                                                
  inner join #CustomerAddress ca                            
    on ca.customerId = c.customerId                                                               
  inner join #Address a                            
    on ca.ADDRESSID = a.ADDRESSID) As D
	WHERE rownum = 1;

Open in new window


temp tables:
create table #Customer (CUSTOMERID int, CUSTOMERNAME varchar(10));
create table #Address (ADDRESSID int, STREET1 varchar(50), STREET2 varchar(20),
                       CITY varchar(20), STATE varchar(2), ZIPCODE  varchar(9));
create table #CustomerAddress (CUSTOMERID int, ADDRESSID int, ADDRESSTYPE varchar(10));

insert into #Customer(CUSTOMERID, CUSTOMERNAME) values 
(1,'bob'),
(2,'kate'),
(3,'rowan');

insert into #Address(ADDRESSID, STREET1, STREET2, CITY, STATE, ZIPCODE) values 
(1, '123 BobAndKates apartment','13th Floor', 'MyCity', 'MA', '123456789'),
(2, '123 Bobs job','-', 'MyOtherCity', 'MA', '123456780'),
(3, '567 Rowans job','suite 100r', 'boston', 'MA', '02115');

insert into #CustomerAddress(CUSTOMERID, ADDRESSID, ADDRESSTYPE) values 
(1, 1, 'home'),
(2, 1, 'home'),
(1, 2, 'work'),
(3, 3, 'work');

select * from #Customer;
select * from #Address;
select * from #CustomerAddress;

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
And here is the CTE version of the same query: (Check the field and table names and make sure to remove #s from the table names)
;With D AS
(
Select	
    ROW_NUMBER() OVER(PARTITION BY c.CUSTOMERID
    ORDER BY ADDRESSTYPE Desc) AS rownum,                                
    c.CUSTOMERID
    , c.CUSTOMERNAME
    , a.ADDRESSID
    , a.STREET1 
    , a.STREET2
    , a.CITY
    , a.[STATE]
    , a.ZIPCODE
    , ca.ADDRESSTYPE                          
From #Customer c                                                
Inner join #CustomerAddress ca                            
On ca.customerId = c.customerId                                                               
Inner join #Address a                            
On ca.ADDRESSID = a.ADDRESSID)
SELECT D.CUSTOMERID
    , D.CUSTOMERNAME
    --, D.ADDRESSID
    , D.STREET1
    , D.STREET2
    , D.CITY
    , D.[STATE]
    , D.ZIPCODE
    --, D.ADDRESSTYPE
FROM D
WHERE rownum = 1;

Open in new window

awking00Information Technology SpecialistCommented:
eghtebas' query should work (although I would not use rownum as the alias for the row_number() ... as it is a pseudo column in Oracle and has a different meaning) as long as the address types can only be work or home (hence the descending order). It is also quite similar to slightwv's suggestion that could also be modified using decode just to shorten the query (partition by customerid order by decode(addresstype,'work',1,'home',2)), which has the advantage of being further modified in the event there more than two address types.
Mike EghtebasDatabase and Application DeveloperCommented:
Hi awking00,

Thanks for the comments. daveslash can change rownum to rn. Regarding additional address type, I have stayed with requirements stated by daveslash. This has helped to keep the solution simple.

Mike
slightwv (䄆 Netminder) Commented:
>> It produces what you are asking for.

Honestly I really don't see much of a difference between what I posted and what you posted.

The only real difference I see is that I force the sort order in the row_number call instead of alphabetical.  Just in case a third address type of 'alpha centauri'  needs to be selected last.


Also:  There is quite a lot of editing to get that SQL Oracle ready.  Yes, rownum causes an error.  You also need to remove the '#' and square brackets to get it running.

Once I corrected the errors, it appears to return the exact same data as mine.
Mike EghtebasDatabase and Application DeveloperCommented:
I generally make a temp table when I see a question because I want to test it before I post it. This takes a bit more time before I could post it. I had asked Dave to remove # tags.

I am glad my results matches what you have.

Mike
slightwv (䄆 Netminder) Commented:
>>I generally make a temp table when I see a question because I want to test it before I post it.

As do I.  I just didn't see the need to post my test setup.

It was still pretty much the exact same solution.

>>I had asked Dave to remove # tags.

And the square brackets?
Mike EghtebasDatabase and Application DeveloperCommented:
STATE was colored differently so I though it requires to be either [STATE] or 'STATE' to be on the safe side. BTW, FYI, I had it tested in SQL Server. Now I know it should be removed for Oracle. Thanks for the info.

I had to go through some trial and error until I could figure out what to do. See, I am not as experienced as you are so it took me longer.

But you have to give me credit in my choice of sorting because it is faster and simpler than use of case. I know case gives the choice of some other types of address but that is beyond the requirement at this point.

I included my temp table script for the benefit of future readers of this tread so they can use it if they need too. I think this is good for EE.

Mike
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
This is exactly why I love Experts Exchange. All of you have been MASSIVELY helpful, and each solution "built on" the previous ones to make it a little better. The final result  is exactly what I need,.

Interestingly, this query will ultimately run on Oracle, but since I've been a DB2-guy for that last couple decades, I'm really looking for something that'll work in both environments, and I think I have it. I'll post my final query.

Thank you VERY much!

-- DaveSlash
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
Here's the final query:

With tempTable AS (
Select ROW_NUMBER() OVER(PARTITION BY c.CUSTOMERID
                         ORDER BY case ca.addresstype
                                    when 'work' then 1
                                    when 'home' then 2
                                  end) as rn,
       c.CUSTOMERID, 
       c.CUSTOMERNAME, 
       a.ADDRESSID,
       a.STREET1,
       a.STREET2,
       a.CITY,
       a.STATE,
       a.ZIPCODE,
       ca.ADDRESSTYPE
  From Customer c
  join CustomerAddress ca
    On ca.customerId = c.customerId
  join Address a
    On ca.ADDRESSID = a.ADDRESSID)
SELECT t.CUSTOMERID, 
       t.CUSTOMERNAME,
       t.STREET1, 
       t.STREET2, 
       t.CITY, 
       t.STATE,
       t.ZIPCODE
  FROM TempTable t
 WHERE rn = 1
;

CUSTOMERID   CUSTOMERNAME  STREET1                    STREET2                    CITY             STATE  ZIPCODE  
         1    bob          321 Bobs job               -                          MyOtherCity       MA    123456780
         2    kate         123 BobAndKates apartment  13th Floor                 MyCity            MA    123456789
         3    rowan        567 Rowans job             suite 100                  boston            MA    02115    

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.