CODEWITHSUNDEEP
sqlsql-serverjoin
M Winch
M Winch

Reputation: 81

Conditional Join if Exists

I need to join two tables together based on a three-column key stack. The problem is sometimes one of the key columns is translated and mapped differently in another table. I will attempt to example my issue using code:

select t1.TQ
  from table1 t1
  left join table2 t2 on t1.comp_cd = t2.comp_cd and t1.plcy_frm = t2.plcy_frm 
                            and t1.val_cd = t2.val_cd

The columns "comp_cd" and "plcy_frm" are fine, however the problem is with val_cd. Sometimes the val_cd in table2 does not map correctly to table1 and must go through a third table, table3. Table3 structure is below:

Val_Cd        Mapped_Val_Cd
A123         A564

So -> I need to join on Mapped_Val_Cd value when it exists in Table3, but join on Val_Cd from Table2 when Val_Cd does not exist in Table3.

I hope this makes sense - I have tried Case when exists syntax but cannot get that to work.

Upvotes: 3

Views: 3354

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Assuming there are no duplicates in table3, you can left join it in and then choose the value that you want in the on clause:

select t1.TQ
from table1 t1 left join
     table3 t3
     on t1.val_cd = t3.val_cd
     table2 t2
     on t1.comp_cd = t2.comp_cd and
        t1.plcy_frm = t2.plcy_frm and
        t1.val_cd = coalesce(t3.Mapped_Val_Cd, t2.val_cd);

Upvotes: 2

Related Questions