Using Reference table to look up various codes in a column

Question

Using Reference table to look up various codes in a column

jbon007

SSChasing Mays

Points: 602
More actions
February 18, 2011 at 3:30 pm

#247047

Hi,
i have two tables:
table 1 has codes that go vertically e.g
table1codes
x34
x32
a33
h01
table 2 has codes that go horizontally e.g
table2codes
j33,s35,m23,p33
is there quick way of searching in table2 codes after the comma using table1? can someone give me example sql so i can apply to my sql based on the above example?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I would shred the comma separate list using a split string function and query the result. Search for DelimitedSplit8K here on SSC for a great example of a split string function.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

jbon007 SSChasing Mays Points: 602 More actions · Answer 2

Thanks for the reply i don't fully understand that code for ssc

This is what i got for if the value was single string and works

codes

w811

s222

f224

SELECT Procedures, Crn, admission

FROM tbl_PS_IXP_MEDREC_PROCS_FLAT

WHERE (Procedures IN

(SELECT OPCSCode

FROM dbo.R_FD_BADS))

is there anyway to adapt the above code so it can look for codes if they were mulitple:

codes

w233,w342,w999

s433,g431

i tried doing using a wildcard

SELECT Procedures, Crn, admission

FROM tbl_PS_IXP_MEDREC_PROCS_FLAT

WHERE (Procedures Like

(SELECT '''' + OPCSCode + '%'''

FROM dbo.R_FD_BADS))

but unfortunately it doesn't like this

jbon007 SSChasing Mays Points: 602 More actions · Answer 3

I think i've cracked it...but needs testing

SELECT F.Crn, F.admission, F.Procedures

FROM tbl_PS_IXP_MEDREC_PROCS_FLAT AS F INNER JOIN

tbl_PS_IXP_ADMS_AND_DIS ON F.Crn = tbl_PS_IXP_ADMS_AND_DIS.crn AND

F.admission = tbl_PS_IXP_ADMS_AND_DIS.admission RIGHT OUTER JOIN

dbo.R_FD_BADS AS BD ON F.Procedures LIKE '%'+ BD.OPCSCode + '%')