Qualifying columns with table names in sql query

If I have a table named "Salary" with two columns named "Payment" and "Rank",

for the following select statement, why do I have to qualify the second parameter "Rank" in my select statement with the table name "Salary"
and I don't have to do that for the first parameter "Payment"

Here is the correct sql statement :



select Payment, Salary.Rank from Salary INNER JOIN Ranks ON Salary.Rank=Ranks.Rank
                                INNER JOIN Jobs ON Jobs.Title = Ranks.Title                              Where Jobs.Employee = 'Jim Baker'


And here is the incorrect sql statement which gives an error message of :
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Rank'.
Note I know that we need the table name in front of the column "Rank", but Why do we not
need to do this for the parameter "Payment" ?

select Payment, Rank from Salary INNER JOIN Ranks ON Salary.Rank=Ranks.Rank
                                INNER JOIN Jobs ON Jobs.Title = Ranks.Title                              Where Jobs.Employee = 'Jim Baker'
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
tigin44Commented:
if you a referencing a column name which is same for two or mare tables you must define the column qualifier to indicate to the server which column of which table you are referencing.  Otherwise you you will always get the error message.

Msg 209, Level 16, State 1, Line xx
Ambiguous column name 'columnName'.
0
 
Anthony PerkinsCommented:
And in general it is always good practice to qualify all columns when there is more than one table involved.  In addition alias should be used and RANK should be enclosed in [].  As in:
SELECT  p.Payment,
        s.[Rank]
FROM    Salary s
        INNER JOIN Ranks r ON s.[Rank] = r.[Rank]
        INNER JOIN Jobs j ON r.Title = j.Title
WHERE   j.Employee = 'Jim Baker'

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.