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

Cheers

Answers


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.


Need Your Help

PetaPoco Paged query generation improvement

c# sql petapoco

I am big fan of PetaPoco and simply amazed when I see the code that is written in it. However, while using it for real life project I faced an issue wherein I had a query that was something like th...

How can I only allow certain filetypes on upload in php?

php upload file-type

I'm making a page where the user upload a file. I want an if statement to create an $error variable if the file type is anything other jpg, gif, and pdf.