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.
| 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_ProviderIDFrom [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_idMind 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 |
 |
|
|
|
|
|
|
|
Using CASE in Join statment