CODEWITHSUNDEEP
sqlsql-serverstringsubqueryleft-join
SOK
SOK

Reputation: 1792

Concatenate in SQL and then use in Left Join

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!

Upvotes: 0

Views: 1948

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Use a VALUES() clause and CROSS APPLY. It is pretty much designed for this:

SELECT a.*, v.Concatenate, b.*
FROM [xxxx].[dbo].[xxxx] a LEFT JOIN
     ([xxxx].[dbo].[xxxx] b CROSS APPLY
      (VALUES (CONCAT(b.FirstName, ' ', b.LastNameNoSuffix))
      ) v(concatenate)
     ) 
     ON v.Concatenate = a.[display_name]

Upvotes: 0

PSK
PSK

Reputation: 17943

You can't use Concatenate in your LEFT JOIN, you can do it like following using a SUB SELECT or a CTE function

SELECT a.*,b.*
FROM [xxxx].[dbo].[xxxx] a 
LEFT JOIN (
    SELECT t.*
        ,CONCAT (
            t.FirstName
            ,' '
            ,t.lastname
            ) AS Concatenate
    FROM [xxxx].[dbo].[yyyyy] t
    ) b ON b.Concatenate = a.[display_name]

Note: If you are using SQL Server, use single quote instead of double quotes in your CONCAT function.

Upvotes: 2

GMB
GMB

Reputation: 222722

You cannot reuse an alias defined in the SELECT clause in the same scope (left apart the ORDER BY clause). You can either use a subquery (which creates a new scope), or repeat the expression. I find that the computation is simple enough so the second solution is good enough:

select a.*, b.firstname + ' ' + b.lastnamenosuffix as concatenate, b.*
from a
left join b on b.firstname + ' ' b.lastnamenosuffix = a.display_name

Notes:

  • don't use double quotes for literal strings! Use single quotes only, as specified in standard SQL

  • SQL Server supports + for string concatenation, which shortens the expression a little

Upvotes: 1

VBoka
VBoka

Reputation: 9083

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

Upvotes: 3

Related Questions