Use the results from a Substring in a Left Join

Question

Use the results from a Substring in a Left Join

netguykb

Ten Centuries

Points: 1310
More actions
April 23, 2020 at 3:29 pm

#3745687
Hey Folks, did. a little digging around however I was not able to find a simple solution for this.
I have a column called [Composite Med Procedure ID] that is being SUBSTRINGED to remove erroneous data and renamed to [Unique CPT]. I want to then use the results in the JOIN clause to bring in the description from another table. If Don't rename [Composite Med Procedure ID] then the SQL results display NULL value in Description because it's not using the SUBSTRINGed Values. I then tried renaming the [Composite Med Procedure ID] to [Unique CPT] but for obvious reasons the JOIN clause fails and indicates the Column doesn't exist. I get why
So how can I use the results from the SUBSTRING in the JOIN Clause
Thank you!
```
SELECT DISTINCT A.[Organization Name], 
SUBSTRING(A.[Composite Med Procedure ID],4,5) [Unique CPT],
B.DESCRIPTION

FROM dbo.CCN_837 A
LEFT JOIN dbo.CPT_CODES B ON [Unique CPT]=[HCPCS]
```

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

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

netguykb Ten Centuries Points: 1310 More actions · Answer 1

Sorry figured it out. I guess I needed to perform the substring in the JOIN clause DUH.

SELECT DISTINCT A.[Organization Name], 
SUBSTRING(A.[Composite Med Procedure ID],4,5) [Unique CPT],
B.DESCRIPTION

FROM dbo.CCN_837 A
LEFT JOIN dbo.CPT_CODES B ON SUBSTRING(A.[Composite Med Procedure ID],4,5) = [HCPCS]

Ken McKelvey SSCoach Points: 18910 More actions · Answer 2

CROSS APPLY can be used to make the multiple use of the same expression more readable:

SELECT DISTINCT N.[Organization Name], 
X.UniqueCPT,
C.[DESCRIPTION]
FROM dbo.CCN_837 N
CROSS APPLY ( VALUES(SUBSTRING(N.[Composite Med Procedure ID], 4, 5)) ) X (UniqueCPT)
LEFT JOIN dbo.CPT_CODES C
ON X.UniqueCPT = C.HCPCS;

Also, put an alias against all columns and try to avoid spaces in column names.

This reply was modified 5 years, 1 month ago by Ken McKelvey.

Use the results from a Substring in a Left Join

Cookies on SQLServerCentral