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'
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';