Category: SQL Server

01 Jun 2016 22:35

Joined:	23/08/2014
Level:	Silver
Points:	104

Points:	3

Ambiguous column name 'Associate_ID'.

select Associate_ID as assid,IsActive,value from
[CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] AP
inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD
ON CAD.[Associate_ID]=AP.[Associate_ID]

when i run the above query shows error as follows

Ambiguous column name 'Associate_ID'.

please help me what is the problem in my above code

#766291

01 Jun 2016 23:13

Joined:	15/12/2015
Level:	Gold
Points:	1308

Points:	3

Hi,
Its because you are performing join on same table and the case is that 'Associate_ID' exists in more than one table in your query.
Just specify the table name or its alias to the front of your column to fix this error.
For Eg.


select CAD.Associate_ID as assid,IsActive,value from 
 [CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] AP
inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD 
ON CAD.[Associate_ID]=AP.[Associate_ID]

#766292

01 Jun 2016 23:33

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	3

Hi

you have same column name in different table so you must use alias name

i have mention sample script for this


SELECT * FROM TB_COUNTRY a inner join TB_STATE b on a.id=b.id

This is your Query


select AP.Associate_ID,IsActive,value from 
[CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] AP
inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD 
ON CAD.[Associate_ID]=AP.[Associate_ID]

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#766295

02 Jun 2016 00:03

Joined:	05/06/2009
Level:	Gold
Points:	10245

Points:	4

If you are seeing any "Ambiguous" related to the column, it is because of the same column name exist in more that one table. In this case you can to specify the table name in the selection. In your case "Associate_ID" may exst in the "vw_CentralRepository_Associate_Details" and "vw_CentralRepository_Contacts". But in the selection you are selection as "Associate_ID as assid". But it should be "AP.Associate_ID" or CAD.Associate_ID

You can select it as following two ways


select AP.Associate_ID as assid,IsActive,value from 
[CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] AP
inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD 
ON CAD.[Associate_ID]=AP.[Associate_ID]


select AP.Associate_ID as assid,IsActive,value from 
[CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] AP
inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD 
ON CAD.[Associate_ID]=AP.[Associate_ID]

By Nathan
Direction is important than speed

#766306

02 Jun 2016 02:51

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	4

Hi,

The root cause of this problem means while joining more than one table, and when you call columns in selected list you should be call with the table instance, if any one column is available more than one table then obviously you will get ambiguous column error only.

To resolve this you need to re-correct your syntax / query like below.


 select AP.Associate_ID as assid,IsActive,value 
from  [CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] AP
             inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD 
             ON CAD.[Associate_ID]=AP.[Associate_ID]

Note: If you want the AssociateID from "vw_CentralRepository_Contacts" table then you need to pass CAD as instance while calling AssociateID.

Hope this will helpful to you....

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#766513

17 Jun 2016 02:02

Joined:	12/12/2013
Level:	Gold
Points:	529

Points:	4

Hi,
select Associate_ID as assid,IsActive,value from
[CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] AP
inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD
ON CAD.[Associate_ID]=AP.[Associate_ID]

In that Query, there is no problem. AssociateID Column sholud appear in both tables. So, while fetching the You have to specify the tablename or alias name of the table. Pls, Use the Below Query,
select CAD.Associate_ID as assid,IsActive,value from
[CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] AP
inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD
ON CAD.[Associate_ID]=AP.[Associate_ID]

OR
select AP.Associate_ID as assid,IsActive,value from
[CentralRepository].[dbo].[vw_CentralRepository_Associate_Details] AP
inner join [CentralRepository].[dbo].[vw_CentralRepository_Contacts] CAD
ON CAD.[Associate_ID]=AP.[Associate_ID]

Regards,
Karunanidhi.K

#766604

21 Jun 2016 03:02

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	3

This error occurred when.
- same column name is present in two or more different tables
- you have used that column name in select and in join
the good way to resolve this issue is use allies
OR
You can use tablename.columnnae for all columns (in selection,where,group by and order by) without using any alias. However you can use an alias as guided by other answers

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

#766607

21 Jun 2016 04:26

Joined:	29/01/2014
Level:	Silver
Points:	346

Points:	2

Hi

Can you try query using column name with table you will not get any Ambiguous error.

For Example.
Select E. Empid,E. EmpName,ES. JobTitle,ES.Salary from Employee as E inner join EmployeeSalary as ES on E.Empid=ES.EmpID

Ambiguous column name 'Associate_ID'.

About dotnetspider.com

Quick Links

General

Services