5

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.

Community
  • 1
  • 1
Jack
  • 2,153
  • 5
  • 28
  • 43

1 Answers1

10

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
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    Is there any way of referencing both `name` columns with the alias? The examples in my question are simplified - I use the alias elsewhere in the query so need to leave it in. – Jack Oct 21 '12 at 23:59
  • i have updated the answer. follow-up question, why would you need to have an alias for both tables that have the same column name? basically you cannot do that. you need to create a subquery but first add an alias on the same column name so you call them easily. – John Woo Oct 22 '12 at 00:07
  • 1
    Aha your second example gave me what I needed. I was basically doing a subquery on `a` again in the `WHERE` clause, equating columns in the first `a` and second `a`, so I needed the alias. Instead of aliasing the whole join I just aliased the first `a` which solved my problem. – Jack Oct 22 '12 at 00:11