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

user_13194245 0
Viewed 2456

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!

sql-server sql
5 Answers

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
user_12080340 
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 SUBSTRING(a.[textname],CHARINDEX('/ ',a.[textname])+1,11) = c.FirstName

First, join takes place and can't find the name

user_133908560

I think you should try this in your ON CLAUSE:

SUBSTRING(a.[textname],CHARINDEX('/ ',a.[textname])+1,11)

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 SUBSTRING(a.[textname],CHARINDEX('/ ',a.[textname])+1,11)  = c.FirstName
user_132720800

You can't reference the column alias name on the same level.

You have to use the entire expression on the ON

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

Or you can make use of a CTE or derived query and compose the expression name in the CTE or derived query.

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

Or alternatively use a CROSS APPLY to compute the expression and then use it in the JOIN

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

As the order of execution of SQL query says the select statement is run after all join statements so you can use alias in select for manipulation but not the same manipulated alias in join as join has already run by that time instead ypu need to have an extra select on top of it or using a WITH CLAUSE Subquery

   Select *, name from ( select *,.. as 
     name from table)  left join othertable
    t1 on 
    name=t1.value

Or using WITH CLAUSE

     WITH data as 
     ( select *,.. as name from table) 

    Select * from data left join table1 t1
    on data.name=t1.value
user_104974830
Related