| Author |
Topic |
|
jcb267
Constraint Violating Yak Guru
291 Posts |
 Posted - 2009-03-17 : 17:23:13
|
| I am using SQL 2005 to run a query that will need multi-evel joins because one of the tables could have multiple records associated with it. Here is the query I am running so far:select substring(hosp_nmrc.wksht_cd, 1, 1) as Worksheet, case when substring(hosp_nmrc.wksht_cd,1,2) like '[A-Z][1-9]' then substring(hosp_nmrc.wksht_cd, 1, 2) else '' END as Worksheet2, substring(hosp_nmrc.wksht_cd, 1, 3) as Worksheet3, case when substring(hosp_nmrc.wksht_cd,1,3) like '[A-Z][1-9]' then substring(hosp_nmrc.wksht_cd, 1, 3) else '' END as Worksheet4, hosp_nmrc.wksht_cd as wksht_cd_HCRIS, hosp_nmrc.line_num as Line, hosp_nmrc.clmn_num as Col, hosp_nmrc.itm_val_num as [Value], hosp_rpt.prvdr_num as MCR_NUM, hosp_rpt.fy_end_dt as FYE,hosp_rpt.fi_num as FI_NUM, hosp_rpt.FI_RCPT_DT as FI_RCPT, substring(hosp_rpt.prvdr_num, 1, 2) as State_Code, statecodes.state_namefrom statecodes left join hosp_rpt on statecodes.ssa_state_cd_0 = (substring hosp_rpt.prvdr_num, 1, 2) or hosp_rpt on statecodes.ssa_state_cd_1 = (substring hosp_rpt.prvdr_num, 1, 2) or hosp_rpt on statecodes.ssa_stae_cd_2 = (substring hosp_rpt.prvdr_num, 1, 2) left join hosp_nmrc on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUMThis join does not work, can anyone help me with it?Thanks! |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-03-18 : 01:26:51
|
| [code]select columnnames,..................from statecodes left join hosp_rpt on statecodes.ssa_state_cd_0 = substring (hosp_rpt.prvdr_num, 1, 2) or statecodes.ssa_state_cd_1 = substring (hosp_rpt.prvdr_num, 1, 2) or statecodes.ssa_stae_cd_2 = substring (hosp_rpt.prvdr_num, 1, 2)left join hosp_nmrc on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM[/code] |
 |
|
|
|
|
|
Multi-level joins