CODEWITHSUNDEEP
sqlsql-servert-sqlsyntaxalias
systemaddict
systemaddict

Reputation: 363

SQL Server/TSQL : Aliasing a column on table alias in join

For efficiency, I'm trying to find out if there is a syntax which allows me to alias a column on a table using that table alias entirely in the from section and join sections.

I know I could alias the column in the SELECT, but if I were using select * (I know, not good practice) it would not be available. I boiled down an example to show how I'd like to refer to the column name (doubleAlias):

SELECT * 
FROM [table1] AS tl
   JOIN [table2] AS t2
   ON t1.[column1] = t2.[column1] AS doubleAlias
    WHERE doubleAlias = 'value';
--INSTEAD OF
  --WHERE t2.[column1] = 'value';

Upvotes: 1

Views: 2109

Answers (3)

sam yi
sam yi

Reputation: 4934

You can wrap the table in a CTE where you can assign new column names.

WITH t2 AS (
    SELECT *, column1 AS doubleAlias
    FROM [table2] 
)
SELECT *
FROM [table1] AS tl
JOIN t2
   ON t1.[column1] = t2.[doubleAlias]
WHERE t2.doubleAlias = 'value';

Upvotes: 2

user3112728
user3112728

Reputation: 405

There are at least two methods of double aliasing a column, just not with the syntax you chose.

  1. Consider replacing table2 with a view, you can alias the columns as much as you want within the view.

  2. Consider a sub query, so replace table2 with another select statement

    select * from A join (SELECT Col1 ColAlias from B) C on A.ColName = ColAlias

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

In SQL Server, you can use outer apply:

SELECT t1.*, t2.*
FROM [table1] tl JOIN
     [table2] t2
     ON t1.[column1] = t2.[column1] OUTER APPLY
     (VALUES (t1.column1) ) v(doubleAlias)
WHERE doubleAlias = 'value';

Upvotes: 1

Related Questions