Concatenate in SQL and then use in Left Join

Question

I have the following SQL code:

SELECT a.*,CONCAT(b.FirstName, " ", b.LastNameNoSuffix) AS Concatenate, b.*

  FROM [xxxx].[dbo].[xxxx] a

  Left Join [xxxx].[dbo].[xxxx] b
  on b.Concatenate= a.[display_name]

But I am getting an Invalid column name error

Hopefully its something simple but I cannot seem to work it out? Thanks!

Answer 1

This is how you can do what you need:

SELECT a.*, b.*
FROM test a
join (select t2.*
             , CONCAT(t2.FirstName, ' ', t2.lastname) AS Concatenate
      from test2 t2) b on b.Concatenate = a.[display_name]

And here is a small demo:

DEMO