# Multiple joins for fields that need the same data. SQL Server 2008 R2

I'm hoping I can find a better way to do this. I've tried this with a join with multiple 'OR' statements (I know it's terrible), and with multiple joins as shown below.

SELECT  ICD_CODE1, ICD_CODE2, ICD_CODE3, icd1.DESCRIPTION AS ICD_DESCRIPTION1,
icd2.DESCRIPTION AS ICD_DESCRIPTION2, icd3.DESCRIPTION AS ICD_DESCRIPTION3
FROM ICD_CODES
LEFT OUTER JOIN ICD_DECRIPTIONS AS icd1
ON ICD_CODES.ICD_CODE1 = icd1.ICD_CODE
LEFT OUTER JOIN ICD_DECRIPTIONS AS icd2
ON ICD_CODES.ICD_CODE2 = icd2.ICD_CODE
LEFT OUTER JOIN ICD_DECRIPTIONS AS icd3
ON ICD_CODES.ICD_CODE3 = icd3.ICD_CODE


This takes place in a much larger dataset and is terribly slow. There are other fields that need a similar style join so I'm hoping someone can help me optimize.

Classic de-normalized data. I've seen this often in healthcare. You can create a function to do this for you. It's unlikely to be faster but will save your some typing. You'd have to test each to see which is quicker. But the bottom line is there isn't a "great" improvement method.

create function dbo.get_icd_code (@icd varchar(16))
returns varchar(256)
as
begin
declare @return varchar(256)
select @return = DESCRIPTION
from ICD_DESCRIPTIONS
where ICD_CODE = @icd
return @return
end


Then call it...

SELECT
ICD_CODE1,
dbo.get_icd_code(ICD_CODE1)
ICD_CODE2,
dbo.get_icd_code(ICD_CODE2)
ICD_CODE3,
dbo.get_icd_code(ICD_CODE3)
FROM ICD_CODES


Another way to speed it up is to stage your data first since there are so many ICD10 codes, but you'll need to provide the entire query for me to determine if this will work--primarily what other tables you are joining to and limiting from.

Lastly, you could try to un-pivot your data, join to the description field once, and then either keep it in this vertical fashion or pivot it back up. Again, hard to benchmark this against the multiple joins without being in your environment.

• Thank you for the reply. I previously attempted the scalar function route and it was significantly less performant. I think the best way would probably be the unpivot/pivot route but it's so messy since the are 8 ICD codes and 4 CPT/HCHC codes. – Chandler Turner Jun 20 '17 at 21:37
• I understand that. We did something similar in our HIS and there are other places where this logic has to exist unfortunately. If you are only doing this for a set amount of visits you can get some performance increase by loading those visits into a temp table and then using that to table as your base table joining to the CPT code description. It really just depends on the complexity of your over all query – scsimon Jun 20 '17 at 21:44
• Yeah, this particular query is quite complex. I went ahead and accepted your answer even though it isn't the 'magic solution I was hoping for. Thank you again for your input. – Chandler Turner Jun 21 '17 at 14:21
• Thanks. Unfortunately I don't think there is a magical solution aside from creating a view and indexing it which will put it on disk. I'm not a fan of this though. – scsimon Jun 21 '17 at 14:29