When I join to the table for APPID I get a ambiguous column error. If I put the prefix for the alias I get invalid column. What's the correct syntax for a join with the same column in multiple tables?
SELECT TOP (1000) [TH.APPID] --APPID ambiguous column, invalid column TH.APPID
,NM.LAST_NAME
,NM.FIRST_NAME
,[SOURCE_CDE]
,[GROUP_NUM]
,[TRANS_KEY_LINE_NUM]
,[TRANS_DTE]
,[TRANS_AMT]
,[TRANS_DESC]
,[FOLIO]
,[ACCT_CDE]
,[PROJECT_CODE] --ambiguous column
,[ENCUMB_GL_FLAG]
,[ENCUMB_GL_TRANS_ST]
,[AP_SBS_ID_NUM]
,[AP_SBS_CDE_SUBSID]
,[INVOICE_NUM]
,[ID_NUM] --ambiguous column
,[SUBSID_CDE]
,[OFFSET_FLAG]
,[SUBSID_TRANS_STS]
,[PAYABLE_CHECK_DTE]
,[CHECK_NUM_ALPHA]
,[CHECK_NUM_NUM]
,[TRANS_PO_NUM_GRP_N]
,[PO_LINE_NUM]
,[DISCOUNT]
,[RECEIPT_NUM]
,[ABA_NUM]
,[AR_CDE]
FROM [TmsePrd].[dbo].[TRANS_HIST] TH
INNER JOIN DIVISION_DEF DD ON TH.APPID = DD.APPID
INNER JOIN SECTION_MASTER SM ON TH.APPID = SM.APPID
INNER JOIN NAME_MASTER NM ON TH.APPID = NM.APPID AND TH.ID_NUM = NM.ID_NUM
WHERE DIV_CDE = 'DC'
AND DIVISION_CDE = 'UG'
OR DIVISION_CDE = 'GR'
AND TH.TRANS_DESC = 'SubTerm A Technology Fee'
OR TH.TRANS_DESC = 'SubTerm B Technology Fee'
Member
18 Points
50 Posts
T-SQL ambiguous column, invalid column in inner join
Jul 09, 2019 04:44 PM|Tom4IT|LINK
When I join to the table for APPID I get a ambiguous column error. If I put the prefix for the alias I get invalid column. What's the correct syntax for a join with the same column in multiple tables?
SELECT TOP (1000) [TH.APPID] --APPID ambiguous column, invalid column TH.APPID
,NM.LAST_NAME
,NM.FIRST_NAME
,[SOURCE_CDE]
,[GROUP_NUM]
,[TRANS_KEY_LINE_NUM]
,[TRANS_DTE]
,[TRANS_AMT]
,[TRANS_DESC]
,[FOLIO]
,[ACCT_CDE]
,[PROJECT_CODE] --ambiguous column
,[ENCUMB_GL_FLAG]
,[ENCUMB_GL_TRANS_ST]
,[AP_SBS_ID_NUM]
,[AP_SBS_CDE_SUBSID]
,[INVOICE_NUM]
,[ID_NUM] --ambiguous column
,[SUBSID_CDE]
,[OFFSET_FLAG]
,[SUBSID_TRANS_STS]
,[PAYABLE_CHECK_DTE]
,[CHECK_NUM_ALPHA]
,[CHECK_NUM_NUM]
,[TRANS_PO_NUM_GRP_N]
,[PO_LINE_NUM]
,[DISCOUNT]
,[RECEIPT_NUM]
,[ABA_NUM]
,[AR_CDE]
FROM [TmsePrd].[dbo].[TRANS_HIST] TH
INNER JOIN DIVISION_DEF DD ON TH.APPID = DD.APPID
INNER JOIN SECTION_MASTER SM ON TH.APPID = SM.APPID
INNER JOIN NAME_MASTER NM ON TH.APPID = NM.APPID AND TH.ID_NUM = NM.ID_NUM
WHERE DIV_CDE = 'DC'
AND DIVISION_CDE = 'UG'
OR DIVISION_CDE = 'GR'
AND TH.TRANS_DESC = 'SubTerm A Technology Fee'
OR TH.TRANS_DESC = 'SubTerm B Technology Fee'
All-Star
123178 Points
9997 Posts
Moderator
Re: T-SQL ambiguous column, invalid column in inner join
Jul 09, 2019 05:25 PM|limno|LINK
TH.[APPID]
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
All-Star
47090 Points
17345 Posts
Re: T-SQL ambiguous column, invalid column in inner join
Jul 09, 2019 05:28 PM|PatriceSc|LINK
Hi,
Each component is escaped separetely ie this is [TH].[APPID].
[TH.APPID] would be column whose name is really "TH.APPID"