You must Sign In to post a response.
  • Category: SQL Server

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

    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]

    By Nathan
    Direction is important than speed

  • #766306
    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
    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
    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
    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


Sign In to post your comments