SQL INNER JOIN Concatenated Field with a column in another table

May 30, 2023

I want to join two tables where column B+C in Table1 = ID column in Table2 In SQL.

I have the following code which returns "Invalid column name ‘TestConcat’"

SELECT A, B, C, concat(B,C) AS TestConcat, D
FROM Table1
INNER JOIN Table2  ON Table1.TestConcat = Table2.ID
WHERE A = '1234'

>Solution :

In SQL, you cannot reference a column alias in the same query level where it was defined. This means that you cannot directly use the column alias ‘TestConcat’ in the ON clause of the JOIN statement. However, you can achieve the desired result by using a subquery or a common table expression (CTE) to perform the concatenation and then join the tables. Here’s an example using a subquery:

SELECT A, B, C, TestConcat, Class
FROM (
   SELECT A, B, C, concat(B, C) AS TestConcat, Class
   FROM Table1
) AS Subquery
INNER JOIN Table2 ON Subquery.TestConcat = Table2.ID
WHERE A = '1234';