Menu
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:
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.
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.
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.
516
One of the simplest ways to solve an “ambiguous name column” error — without changing column name — is to give the tables you want to join an alias. This sends a clear information to the SQL Machine the columns are different.
Ignore the id column as this is an auto-increment column. If we attempt to run a join in the two tables: select full_name, lang, full_name, salary from my_table x inner join my_table_1 y ON x.id = y.id; The above query will return an “ambiguous column name” error.
You can declare an alias for any column in the select list of the Projection clause. The GROUP BY clause can reference the column by its alias. This temporary name is in scope only while the SELECT statement is executing.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
