How do I solve the error Ambiguous column name with join

I’m currently struggling with a join where the column [NAME] results in an error (ambiguous column). I’m pretty new at SQL and someone else made the first join for me, so I now just tried to replicate this with something else I wanted to join and as I understood, I do need to add the columnname of the data I’m trying to join to the table, otherwise it won’t show that datacolumn. I renamed the table to INVENTGROUP, but still I get this error. What am I doing wrong? Many thanks for your help! Below you can find my complete query.

SELECT [SALESID]
,[INVOICEID]
,[INVOICEDATE]
,[LINENUM]
,[PRICEUNIT]
,[DISCPERCENT]
,[LINEAMOUNT]
,[TRANSACTIONCODE]
,[DELIVERYTYPE]
,[LINEPERCENT]
,[DLVDATE]
,[INVENTQTY]
,[INVENTREFTYPE]
,[REMAINBEFORE]
,[QBE_COSTPRICE]
,[QBE_ITEMTXT]
,[MODIFIEDDATETIME]
,[CREATEDDATETIME]
,[ISSM_WARRANTYPACKNAME]
,[QBE_LINEAMOUNTMSTPURCHEXCHDATE]
,[DATAAREAID]
,[ITEMID]
,[QTY]
,[CURRENCYCODE]
,[SALESPRICE]
,[Brand]
,[CostCenter]
,[Department] 
,[NAME]
,INVOICE.[RECID]
,INVOICE.[ITEMGROUPID]
FROM [ELG_DynamicsAX_DWH].[dbo].[CUSTINVOICETRANS] AS INVOICE
JOIN(SELECT[Brand]
,[CostCenter]
,[Department]
,[RECID]
From [ELG_DynamicsAX_DWH].[dbo].[CustInvoiceTransFinDim]) AS FINDIM ON FINDIM.RECID = INVOICE.RECID
JOIN(SELECT[NAME]
,[ITEMGROUPID]
From [ELG_DynamicsAX_DWH].[dbo].[INVENTITEMGROUP]) AS INVENTGROUP ON INVENTGROUP.ITEMGROUPID = INVOICE.ITEMGROUPID

Answer

Can you please check if this table “CUSTINVOICETRANS” also has a column “Name”, same column is also present in table “ELG_DynamicsAX_DWH”. make sure in select you use alias where ever column name is conflicting with 2 tables/ correlated subquery.

Advertisement

Answer