CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
TLD
TLD

Reputation: 8135

ambiguous column error when joining 2 tables which have the same column name

in my table A {ID, FID, Name , Age}
in my table B{ID, Job}

When I use 

SELECT ID // I want to get the ID of table B //**error**//
FROM A TA JOIN B TB
ON TA.ID = TB.FID
ORDER BY TA.Name;

Error: ambiguous column name ID

Is there anyway to do that without having to rename the column name? (The Worst scenario)
Thank you

Upvotes: 4

Views: 12294

Answers (3)

Vinny Roe
Vinny Roe

Reputation: 901

SELECT TB.ID  ...

You need to prefix any ambiguous column anywhere with your table alias.

Upvotes: 5

heximal
heximal

Reputation: 10517

you don't need to rename anything. you just need to specify the source of ambigous field in SELECT clause since such field exists in both sources (tables)

Upvotes: 0

Jacob
Jacob

Reputation: 43219

SELECT TB.ID
FROM A TA JOIN B TB
ON TA.ID = TB.FID
ORDER BY TA.Name;

Use the full name (table name and column name seperated by a dot) to specifically select a column.

Your query doesn't fit your described table structure, I guess you swapped table A with table B.

Upvotes: 6

Related Questions