How to use a created column as a JOIN parameter SQL query

Question

I have 3 Tables of Data which i am trying to Join (TEXTDATA, STOREDATA, SALESDATA). My TEXTDATA has a name string within on of the columns so have created a substring to find it.

I am trying to then used that newly formed string (name) as the basis to join to the SALESDATA table.

Here is my code

SELECT b.*,
       a.text,
       a.textname,
       SUBSTRING(a.[textname], CHARINDEX('/ ', a.[textname]) + 1, 11) AS NAME,
       c.[Sales],
       c.[Customer],
       c.[Class]
FROM   [dbo].[TEXTDATA] a
       INNER JOIN [dbo].[STOREDATA] b
            ON  a.[ID_TEXTDATA] = b.[ID_STOREDATA]
       LEFT JOIN [dbo].[SALESDATA] c
            ON  NAME = c.FirstName

With the error saying Invalid column name 'name'

Any ideas on how to reference the new column made later in a query?

Thanks very much!

Answer 1

Easiest option is just to use the same formula in the LEFT JOIN's ON clause:

LEFT JOIN [dbo].[SALESDATA] c
    ON SUBSTRING(a.[textname],CHARINDEX('/ ',a.[textname])+1,11) = c.FirstName

Or you could put the formula in a CTE abstraction of your TEXTDATA table:

;with cte AS (
    SELECT [ID_TEXTDATA], text, textname, SUBSTRING(a.[textname],CHARINDEX('/ ',a.[textname])+1,11) as name
    From [dbo].[TEXTDATA] a 
)
SELECT b.*
,cte.text
,cte.textname
,cte.name
,c.[Sales]
,c.[Customer]
,c.[Class]
From 
    cte
INNER JOIN [dbo].[STOREDATA] b 
    on  cte.[ID_TEXTDATA] = b.[ID_STOREDATA]
LEFT JOIN [dbo].[SALESDATA] c
    on cte.name = c.FirstName