Search code examples
sqlsql-servermergepyodbcambiguous

Merging two tables in SQL with ProgrammingError of Ambiguous column name

I am trying to merge two tables. I am a bit new at this, but I believe the tables are dbo.Detail and Com.All_Calls_List

dbo.Detail has three columns I want to get: CallID, and ResponsibleParty Com.All_Calls_List has four columns: CallID, CompanyName, Receiveddate, CustomerType

I want to merge the tables by CallID and add ResponsibleParty as a column to the table.

cnxn = pyodbc.connect("DSN=MYDSNCONNECTION")
cursor = cnxn.cursor()

cursor.execute("select CallID, CompanyName, recvd_dttm, CustType, ResponsibleParty from dbo.Detail, Com.All_Calls_List where Com.All_Calls_List.CallID = dbo.Detail.CallID")

This is giving a Programming Error of [SQL Server Native Client 10.0][SQL Server]Ambiguous column name 'CallID'. I looked up this error and I understand that it is because CallID is a duplicate column in both tables. I have read up on how other people have solved it, but their tables and goals were different enough that I could not figure out how to apply it here. Thanks for any suggestions.

Solution 
  • select Com.All_Calls_List.CallID, CompanyName, recvd_dttm, CustType, ResponsibleParty
from dbo.Detail join Com.All_Calls_List 
on Com.All_Calls_List.CallID = dbo.Detail.CallID

    It is suggested you use the table-name if a column exists in both tables. Also, as you are joining the tables on callID selecting from either table gives you the same result.