Ambiguous Column Reference with an AS alias

alias, ambiguous, postgresql, sql

Solution

don't enclose it with parenthesis since `(a INNER JOIN b ON a.id = b.id)` is not a complete query.

SELECT  a.name AS A_Name, 
        b.name AS B_Name
FROM    a INNER JOIN b 
           ON a.id = b.id

or (assuming) if you have longer tables names and you want to make it short,

SELECT  a.name AS A_Name, 
        b.name AS B_Name
FROM    longTableNameA a 
        INNER JOIN longTableNameB b 
           ON a.id = b.id

Problem

I am unsure as to how to resolve an ambiguous column reference when using an alias. Imagine two tables, `a` and `b` that both have a `name` column. If I join these two tables and alias the result, I do not know how to reference the `name` column for both tables. I've tried out a few variants, but none of them work: Attempt 1 ``` SELECT a.name, b.name FROM (a INNER JOIN b ON a.id = b.id) AS x ``` This doesn't work as `a` and `b` are out of scope. Attempt 2 ``` SELECT x.a.name, x.b.name FROM (a INNER JOIN b ON a.id = b.id) AS x ``` SQL syntax doesn't work like that. Attempt 3 ``` SELECT x.name, x.name FROM (a INNER JOIN b ON a.id = b.id) AS x ``` That's just plain ambiguous! I'm all out of ideas - any help would be much appreciated.

Original source