CODEWITHSUNDEEP
mysqlsql
user2708932
user2708932

Reputation:

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

Upvotes: 1

Views: 187

Answers (3)

Taryn
Taryn

Reputation: 247710

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.

Upvotes: 0

cdhowie
cdhowie

Reputation: 169018

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.

Upvotes: 0

Jeff Watkins
Jeff Watkins

Reputation: 6359

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.

Upvotes: 1

Related Questions