Multiple Joins - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Multiple Joins

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-17 : 14:50:06

I am trying to create a join on multiple conditions but cannot get it to work. THis is what I have 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_name

from
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_NUM

where
statecodes.ssa_state_cd_0 = '22'

Can anyone help?

pootle_flump

1064 Posts

Posted - 2009-03-17 : 14:54:50

Tricky since we don't know what's wrong!

from
statecodes
left join 
hosp_rpt 
on 
	(
		hosp_rpt.prvdr_num LIKE statecodes.ssa_state_cd_0 + '%'
	OR
		hosp_rpt.prvdr_num LIKE statecodes.ssa_state_cd_1 + '%'
	OR
		hosp_rpt.prvdr_num LIKE statecodes.ssa_state_cd_2 + '%'
	)
LEFT JOIN 
hosp_nmrc 
on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM

This will use your indexes, but assumes that prvdr_num is 2 chars. Also, you know this smacks of a design flaw right? First normal form - heard of it?

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-17 : 15:57:01

quote:
Originally posted by pootle_flump

Tricky since we don't know what's wrong!

from
statecodes
left join
hosp_rpt
on
(
hosp_rpt.prvdr_num LIKE statecodes.ssa_state_cd_0 + '%'
OR
hosp_rpt.prvdr_num LIKE statecodes.ssa_state_cd_1 + '%'
OR
hosp_rpt.prvdr_num LIKE statecodes.ssa_state_cd_2 + '%'
)
LEFT JOIN
hosp_nmrc
on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM
This will use your indexes, but assumes that prvdr_num is 2 chars. Also, you know this smacks of a design flaw right? First normal form - heard of it?

Yes, I do know of the design flaw. I was handed this data and have to work with it though - I didn't design it. Prvdr_num is not 2 characters, it is 6. The substring function is there to correct for this.

pootle_flump

1064 Posts

Posted - 2009-03-18 : 04:02:10

Sorry, I meant the ssa_state_cd_n columns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources