CODEWITHSUNDEEP
sqlsql-servercolumn-alias
pty
pty

Reputation: 121

Use column alias in WHERE clause

I have a function in my query that uses a column in my table and results in a unique number for that input. 

SELECT FunctionResultsinUniqueNumber(t.[table1Column]) AS Col1 
FROM Table1 t

I would also like to join another table which already has that unique number.

SELECT o.[SameUniqueValueAsGeneratedByTheFunctioninTable1] AS Col2
FROM Table2 o

I am not sure if this is possible but I would like to reference the function result to equal the column in Table2 for a successful join: say,

SELECT FunctionResultsinUniqueNumber(t.[table1Column]) AS Col1 
FROM Table1 t
LEFT JOIN Table2 o
ON Col1 = o.[SameUniqueValueAsGeneratedByTheFunctioninTable1]

Thanks for any clarification, help.

Upvotes: 0

Views: 121

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need to call it again with ONclause :

SELECT FunctionResultsinUniqueNumber(t.[table1Column]) AS Col1 
FROM Table1 t LEFT JOIN 
     Table2 o
     ON FunctionResultsinUniqueNumber(t.[table1Column]) =
        o.[SameUniqueValueAsGeneratedByTheFunctioninTable1];

You can also use sub-query :

SELECT FunctionResultsinUniqueNumber(t.[table1Column]) AS Col1 
FROM (SELECT FunctionResultsinUniqueNumber(t.[table1Column]) AS Col1
      FROM Table1 t
      ) t LEFT JOIN
      Table2 t2
      ON t.Col1 = t2.SameUniqueValueAsGeneratedByTheFunctioninTable1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Many databases support lateral joins. I am guessing that you are using SQL Server, which supports these using APPLY. This allows you to move the column definition to the FROM clause:

SELECT . . . 
FROM Table1 t CROSS APPLY
     (VALUES (FunctionResultsinUniqueNumber(t.[table1Column]))
     ) v(col1) JOIN
     Table2 t2
     ON v.Col1 = t2.SameUniqueValueAsGeneratedByTheFunctioninTable1;

Otherwise, you would use a subquery:

SELECT . . . 
FROM (SELECT t.*,
             FunctionResultsinUniqueNumber(t.[table1Column] as col1
      FROM t
     ) t JOIN
     Table2 t2
     ON t.Col1 = t2.SameUniqueValueAsGeneratedByTheFunctioninTable1;

Upvotes: 1

Related Questions