CODEWITHSUNDEEP
sqljoinselect
Dominic Legendre
Dominic Legendre

Reputation: 123

SQL join based on select as column name

So in one table I can use a replace and charindex to extract a specific ID that relates to a PK in another table, I want to then join the data from the other table based on the trimmed value, how can I do this?

select top 100 *, Replace(Left(LogValue,Charindex(';', LogValue) - 1) ,'RtaId=', '') as TaskID, PrmRoutingTask.*    
  from SytLog       
       inner join  PrmRoutingTask on RtaId = TaskID
 where LogTableName like '%Routing%' and LogValue like '%RtaAniId=397%' 
 order by 5 desc;

The problem I get is that the temp column name I create (TaskID) is not working in the inner join where in fact the results of TaskID have the reference to the RtaId in the RoutingTask table.

Upvotes: 0

Views: 45

Answers (1)

The Impaler
The Impaler

Reputation: 48850

Assuming LogValue belongs to the first table you can use the column named TaskID if you produce a subquery as a table expression of the main query.

For example you can produce the column in the table expression a by doing:

select top 100 
  a.*,
  PrmRoutingTask.*

from (
  select *,
    Replace(Left(LogValue,Charindex(';', LogValue) - 1) ,'RtaId=', '') as TaskID
  from SytLog
) a

inner join PrmRoutingTask on PrmRoutingTask.RtaId = a.TaskID

where LogTableName like '%Routing%' 
  and LogValue like '%RtaAniId=397%' 

order by 5 desc

Upvotes: 2

Related Questions