SQLTeam.com | Weblogs | Forums

SQL Conditional Join with multiple key and default non matching value

sql2012

#1

need help with a SQL join.

I have following data :

 Category Curr     Acct Sales
    A     USD      100  400
    A     INR      123  450
    A     CAD      322  475
    B     EUR      456  230
    B     USD      123  550
    B     GBP      125  900
    X     GBP      399 1000

T2   
Category Curr    Acct Customer
    A     USD       % C01
    A       %     123 C02
    A     CAD     322 C03
    B     USD       % C04
    B       %     456 C05
    B     CAD     555 C06
    B       %       % C07
    %       %       % C08
Expected Result of T1 JOIN T2 (Join KEYs are Category, Curr and Acct)

T1.Category T1.Curr T1.Acct T1.Sales T2.Customer   
     A         USD    100     400       C01   
     A         INR    123     450       C02   
     A         CAD    322     475       C03   
     B         EUR    456     230       C05   
     B         USD    123     550       C04   
     B         GBP    125     900       C07   
     X         GBP    399    1000       C08  

I just need to join on Category, Curr, Acct as KEYs but if any of the key value are not matching then we need join on default value as '%' which is shown in the T2 table :

Thanks


#2

This might work for you:

with cte(category,curr,acct,sales,customer,rn)
  as (select a.category
            ,a.curr
            ,a.acct
            ,a.sales
            ,b.customer
            ,row_number() over(partition by a.category
                                           ,a.curr
                                           ,a.acct
                               order by b.category desc
                                       ,b.curr     desc
                                       ,b.acct     desc
                              )
        from t1 as a
             left outer join t2 as b
                          on b.category in (a.category,'%')
                         and b.curr in (a.curr,'%')
                         and b.acct in (a.acct,'%')
     )
select category
      ,curr
      ,acct
      ,sales
      ,customer
  from cte as a
 where rn=1
 order by customer
;