Ambiguous column name 'EmpID'.

Posted on 2009-02-24
Last Modified: 2012-06-27
I'm an SQL rookie.

I'm not seeing what is wrong with the SELECT statement I have.  I am attempting to select from two tables with a single SELECT.

Is the mistake obvious?

I get, "Exception Details: System.Data.SqlClient.SqlException: Ambiguous column name 'EmpID'."

ASP.NET with VB code behind accessing an SQL database file.

"SELECT JobID, JobTitle, EmpID, Employer.EmpID FROM [Jobs], [Employer] WHERE Jobs.EmpID = Employer.EmpID AND([JobType] = 'Full Time Job') ORDER BY [JobTitle]"

Open in new window

Question by:spaceneedlejumper
    LVL 16

    Accepted Solution

    why do you need both of these are the different?
    EmpID, Employer.EmpID
    try this
    SELECT JobID, JobTitle, Employer.EmpID
    LVL 26

    Assisted Solution

    by:Anurag Thakur
    you are getting two empids in the select
    i dont think you will need 2 empids if the values are going to be the same
    if one empid is the employee and the other empid is a manager then give the alias for the manager column as
    LVL 7

    Assisted Solution

    Jobs.EmpID,   -- maybe it is here (the server don't know which table you want to pull the EmpID from so maybe add the table name in the front will help)

    Employer.EmpID FROM [Jobs], [Employer] WHERE Jobs.EmpID = Employer.EmpID AND([JobType] = 'Full Time Job') ORDER BY [JobTitle]
    LVL 7

    Assisted Solution

    I agreed with ragi0017.  You only need to pull one EmpID since the where clause on (WHERE Jobs.EmpID = Employer.EmpID) predetermine the outcome anyway.
    LVL 7

    Assisted Solution

    Ambiguous column name 'EmpID' usually meant that "I don't know which tables you want me to pull the EmpID from--because there are two tables with the same column name".

    Author Closing Comment

    Thanks Everyone!
    LVL 2

    Expert Comment

    Your query is ambiguos because of the EmpID you have used as an orphan while it should be used with table name . coloumn name to access data from it as it is present in both the tables you used. So, we have to explicitly mention that which EmpID we want. And you can use this method to access other fields like jobID,JobTitle But it is not mandatory to do so as it will not create any ambiguity because it is present in only one table not both the table.

    I am attaching the modified query which you could use now without any errors and without many changes to your query

    P.S : Please accept the answer as solution if it has helped you in any way.


    "SELECT JobID, JobTitle, Jobs.EmpID, Employer.EmpID FROM [Jobs], [Employer] WHERE Jobs.EmpID = Employer.EmpID AND([JobType] = 'Full Time Job') ORDER BY [JobTitle]"

    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

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    759 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

    12 Experts available now in Live!

    Get 1:1 Help Now