inner join 2 tables ON charindex

Question

Post reply

inner join 2 tables ON charindex

Michele-148568

SSC Enthusiast

Points: 138
More actions
November 16, 2005 at 3:22 pm

#188449

HELP! HELP! HELP! I have 3 tables I am trying to select values from and inner join into one results page. One of the tables does NOT have a foreign key field to join the two tables, but instead, the value is within a field containing a string of numbers (in a comma delimited format i.e. ,1,2,3,4,5,). The value in between the commas is the same value as an ID value in a field of another table. I need to be able to select only the ID values that match the values I pull from the charindex of the other table's string field. This means only returning the correct value in between the commas that matches with that ID field value. Does that make sense? I've created a query - it doesn't err but it doesn't return any results either. Any suggestions would be greatly appreciated.
SELECT DISTINCT
D.DeptCode,D.DeptName,A.ID as AccessID,A.AccessGroup,A.AccessLevel, F.Group,F.SubGroup,F.DefaultVals

FROM tblD as D
INNER JOIN tblF ON D.DeptCode = F.DeptCode
INNER JOIN tblA ON A.ID in (Select Charindex('," & A.ID & ", ', F.DefaultVals) from tblF where active='1')
WHERE D.Active='1'
AND (F.Active='1')
AND (A.Active='1')
and (A.AccessGroup = 'T')
AND (Charindex('," & A.ID & ",', F.DefaultVals) > 0)
Thanks in advance!

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

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

if it is only commas that is in the field cound you not use replace and join on the values without the commas?

Michele-148568 SSC Enthusiast Points: 138 More actions · Answer 2

Not sure how to add the replace into the mix - can you give me an example of how it would work with this statement ? How can I join the 2 fields after using replace? Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 3

i would fix the table and extract the foreign key, and then add the constrant back tot the table fix the design that is preventing you from doing the right thing with the data.;then fix the application so it inserts the right value into the new FK column.

alternatively, if you can't fix the application to put the values in the right column, i think i would could add a computed column to the table that extracts the id from within the concatenated field, and then join on that;

if you can't fiddle with the schema then stick with extracting the ID as part of your join;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

hmm - jumping into this post only to see if anyone will have a solution that doesn't involve redesigning the tables correctly...

Lowell - do you have time to elaborate on "computed column on table that extracts id.."?!?!

**ASCII stupid question, get a stupid ANSI !!!**