Qualifying columns with table names in sql query

Posted on 2012-09-22
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

    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
    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now