?
Solved

Ambiguous column name 'EmpID'.

Posted on 2009-02-24
7
Medium Priority
?
1,212 Views
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

0
Comment
Question by:spaceneedlejumper
7 Comments
 
LVL 16

Accepted Solution

by:
burakiewicz earned 280 total points
ID: 23723639
why do you need both of these are the different?
EmpID, Employer.EmpID
try this
SELECT JobID, JobTitle, Employer.EmpID
0
 
LVL 26

Assisted Solution

by:Anurag Thakur
Anurag Thakur earned 200 total points
ID: 23723641
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
0
 
LVL 7

Assisted Solution

by:60MXG
60MXG earned 520 total points
ID: 23723668
SELECT
Jobs.JobID,
Jobs.JobTitle,
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]
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Assisted Solution

by:60MXG
60MXG earned 520 total points
ID: 23723701
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.
0
 
LVL 7

Assisted Solution

by:60MXG
60MXG earned 520 total points
ID: 23723721
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".
0
 

Author Closing Comment

by:spaceneedlejumper
ID: 31550652
Thanks Everyone!
0
 
LVL 2

Expert Comment

by:souvik2008
ID: 23724013
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.

Thanks


"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

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

840 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