Using an alias within a join
I'm trying to use an SQL alias within a left join. While using the alias in the join, I get the error 'unknown a_alias column in on clause' Could anyone point me in the right direction?
SELECT a as a_alias FROM table_a LEFT JOIN table_b ON a_alias = b
You can't use the alias in a where or a join as it hasn't been evaluated yet. They're only available in aggregates and group by/order by/having clauses IIRC.
The only aliases you can use in ON are any table aliases; the SELECT clause is first in the grammar, but is (usually) the last processing step; the projection you ask for in the SELECT clause is not available yet. You cannot use column aliases defined in the SELECT clause in an ON clause -- you need to specify the full column name.
If you want to use an alias in a query you can do the following:
SELECT * FROM ( SELECT a as a_alias FROM table_a ) ta LEFT JOIN table_b ON ta.a_alias = b
You can wrap your SELECT ... FROM in parentheses and then use the table alias and the column alias in your JOIN.