Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Using CASE in Join statment

Author  Topic 

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-12-12 : 13:38:39


How can I make a join based off the value of another column?

example: In the below query I need to join clnup.admitting_physician on the providerid field in the provAd table if column clnup.measure_code ='ED-2'. However if clnup.measure_code in('IMM-2', ''STK-8', 'VTE-1', 'VTE-2', 'VTE-5', 'PC-01')I need to join clnup.Attending_Physician on the providerid field in the provAd table if column . And if clnup.measure_code in('OP-6','OP-7') I need to join clnup.Operating_Physician on the providerid field in the provAd table if column .

Select clnup.measure_code
,clnup.admitting_physician
,provAd.provider_id as Admitting_Physician_ProviderID
,clnup.Attending_Physician
,provAd.provider_id as Attending_Physician_ProviderID
,clnup.Operating_Physician
,provAd.provider_id as Operating_Physician_ProviderID

From [rdc_WZ].[dbo].[wt_cm_hospbase_summarydetail_stg_clnup] clnup

Left Join [rdc_WZ].[dbo].[wz_cpm_provider] provAd

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-12 : 14:56:39
... on clnup.admitting_physician and clnup.measure_code ='ED-2' = clnup.Operating_Physician_ProviderID
or clnup.measure_code in('IMM-2', ''STK-8', 'VTE-1', 'VTE-2', 'VTE-5', 'PC-01') and clnup.Attending_Physician = provAd.provider_id

Mind you, this might perform poorly which you will notice if the tables are large. It depends on your indexing. You might find it runs better if you have two separate joins and then union the results
Go to Top of Page
   

- Advertisement -