Learn how to a build a cloud-first strategyRegister Now


Qualifying columns with table names in sql query

Posted on 2012-09-22
Medium Priority
Last Modified: 2012-09-25
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'
Question by:brgdotnet
LVL 26

Accepted Solution

tigin44 earned 220 total points
ID: 38424827
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'.
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 220 total points
ID: 38433882
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,
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


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question