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
 Joins

Author  Topic 

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2009-03-16 : 16:59:06
I am making a join between multiple tables. This is what I have (thanks, vikash16):
from state_codes LEFT JOIN hosp_nmrc on state_codes.Ssa_State_Cd = hosp_nmrc.state_code
LEFT JOIN hosp_rpt on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM

There are actually 3 Ssa_State_Cd fields. StateCodes.Ssa_State_Cd0,
StateCodes.Ssa_State_Cd1, StateCodes.Ssa_State_Cd2. Ssa_State_Cd0 is always populated but Ssa_State_Cd1 and Ssa_State_Cd2 could be null. In the event where there are 3 state codes, how do I ensure that I get records associated with all 3 of them?

For example, California has 3 state codes 5, 55 and 75. I want to get all records associated with all three codes. Can I do that through the join? How would that work?

THanks!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-16 : 17:09:00
Yes. Your join condition would look somewhat like,

LEFT JOIN 
hosp_nmrc on state_codes.Ssa_State_Cd0=...
Or
hosp_nmrc on state_codes.Ssa_State_Cd1=...
Or
hosp_nmrc on state_codes.Ssa_State_Cd2=...


Post sample data,table structures and output if you need more clarity.
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2009-03-16 : 17:12:59
quote:
Originally posted by sakets_2000

Yes. Your join condition would look somewhat like,

LEFT JOIN 
hosp_nmrc on state_codes.Ssa_State_Cd0=...
Or
hosp_nmrc on state_codes.Ssa_State_Cd1=...
Or
hosp_nmrc on state_codes.Ssa_State_Cd2=...


Post sample data,table structures and output if you need more clarity.




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)
LEFT JOIN hosp_rpt on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM

Will this work? I am just getting the substring part to work first, then I will deal with those states that have multiple state codes.

JB
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-03-16 : 17:16:27
Or possibly this - assuming you want a complete, distinct list of all state codes that are in those 3 columns and any associated hosp rows for those states and you don't care which cd0,1,2 column they came from.

from   (
       select distinct ssa_state_cd0 as st_cd from state_codes
       union 
       select distinct ssa_state_cd1 from state_codes
       union 
       select distinct ssa_state_cd2 from state_codes
       ) sc

LEFT JOIN hosp_nmrc hn
       on sh.state_code = sc.st_cd 
LEFT JOIN hosp_rpt hr
       on hr.RPT_REC_NUM = hn.RPT_REC_NUM


state_codes table sounds like it should be a list of all your state codes...why are they in 3 different columns?

Be One with the Optimizer
TG
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-16 : 17:19:03
Which substring part. Can you include some sample data and output or describe a little about your substring problem ?
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2009-03-16 : 17:25:49
quote:
Originally posted by sakets_2000

Which substring part. Can you include some sample data and output or describe a little about your substring problem ?


the substring in the from clause. I do not have any sample data......I have not gotten anything yet.
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-16 : 17:32:35
The substring in your from part,'substring(hosp_rpt.prvdr_num, 1, 2)' is taking the first 2 characters in hosp_rpt.prvdr_num column and comparing that with statecodes.ssa_state_cd_0 .

Say data in hosp_rpt.prvdr_num is 'hello', substring is reducing it to 'he'. Makes sense ?
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2009-03-16 : 17:38:04
quote:
Originally posted by sakets_2000

The substring in your from part,'substring(hosp_rpt.prvdr_num, 1, 2)' is taking the first 2 characters in hosp_rpt.prvdr_num column and comparing that with statecodes.ssa_state_cd_0 .

Say data in hosp_rpt.prvdr_num is 'hello', substring is reducing it to 'he'. Makes sense ?


Yes, it does make sense. The first two characters of a Medicare Provider Number (hosp_rpt.prvdr_num) is a providers state code. So this should return some results?
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2009-03-16 : 17:39:42
quote:
Originally posted by sakets_2000

The substring in your from part,'substring(hosp_rpt.prvdr_num, 1, 2)' is taking the first 2 characters in hosp_rpt.prvdr_num column and comparing that with statecodes.ssa_state_cd_0 .

Say data in hosp_rpt.prvdr_num is 'hello', substring is reducing it to 'he'. Makes sense ?


I am running this query:

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)
LEFT JOIN hosp_rpt on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM

And getting this error:

Msg 1013, Level 16, State 1, Line 1
The objects "hosp_rpt" and "hosp_rpt" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Can you help me to understand what it means?
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-16 : 17:44:40
[code]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)
LEFT JOIN /*hosp_rpt*/ hosp_nmrc on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM [/code]
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2009-03-16 : 18:07:14
quote:
Originally posted by sakets_2000

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)
	LEFT JOIN /*hosp_rpt*/ hosp_nmrc on hosp_rpt.RPT_REC_NUM = hosp_nmrc.RPT_REC_NUM



Thank you very much, that worked perfectly!

JB
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-16 : 18:25:15
np
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2009-03-16 : 21:15:09
quote:
Originally posted by sakets_2000

np

Thanks again!

Now I am ready to tackle the issue of having 3 ssa_state_cd's.
Can you help me with this one?
Will something like what TG suggested work:

select distinct ssa_state_cd0 as st_cd from state_codes
union
select distinct ssa_state_cd1 from state_codes
union
select distinct ssa_state_cd2 from state_codes

JB
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-03-17 : 10:40:55
quote:
Originally posted by jcb267

quote:
Originally posted by sakets_2000

np

Thanks again!

Now I am ready to tackle the issue of having 3 ssa_state_cd's.
Can you help me with this one?
Will something like what TG suggested work:

select distinct ssa_state_cd0 as st_cd from state_codes
union
select distinct ssa_state_cd1 from state_codes
union
select distinct ssa_state_cd2 from state_codes

JB

Are you using sql 2005?
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2009-03-17 : 14:37:42
quote:
Originally posted by visakh16

quote:
Originally posted by jcb267

quote:
Originally posted by sakets_2000

np

Thanks again!

Now I am ready to tackle the issue of having 3 ssa_state_cd's.
Can you help me with this one?
Will something like what TG suggested work:

select distinct ssa_state_cd0 as st_cd from state_codes
union
select distinct ssa_state_cd1 from state_codes
union
select distinct ssa_state_cd2 from state_codes

JB

Are you using sql 2005?


Yes, SQL 2005. Sorry I have been in meetings all day!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site