左外连接"The correlation name 'MMP_PPRVU' has the same exposed name as table 'MMP_PPRVU'."

Question

我已经为此挣扎了一段时间。我相信这与我加入MMP_PPRVU的方式有关。从本质上讲，我正在尝试加入CLARITY_TDL_TRAN-->CLARITY_EAP1-->MMP_PPRVU。以及CLARITY_TDL_TRAN->MMP_STAGING_UNITS->MMP_PPRVU。

所有左侧外部联接。我尝试了多种不同的方法，但无法找到有效的解决方案。非常感谢您的帮助。

SELECT........
FROM  
CLARITY.dbo.CLARITY_TDL_TRAN as CLARITY_TDL_TRAN  
left outer join 
dbo.MMP_STAGING_UNITS as MMP_STAGING_UNITS on MMP_Staging_Units.eTDL_ID = CLARITY_TDL_TRAN.TDL_ID and CLARITY_TDL_TRAN.DETAIL_TYPE in (1, 10)
left outer join
CLARITY.dbo.CLARITY_EAP as CLARITY_EAP on CLARITY_EAP.PROC_ID = CLARITY_TDL_TRAN.PROC_ID
left outer join
CLARITY.dbo.CLARITY_EAP AS CLARITY_EAP1 on CLARITY_EAP1.PROC_ID = CLARITY_TDL_TRAN.PROC_ID --look into this alias
left outer join 
CLARITY.dbo.CLARITY_POS as CLARITY_POS on CLARITY_TDL_TRAN.POS_ID = CLARITY_POS.POS_ID
left outer join
CLARITY.dbo.CLARITY_DEP as CLARITY_DEP on CLARITY_TDL_TRAN.DEPT_ID = CLARITY_DEP.DEPARTMENT_ID
left outer join
CLARITY.dbo.CLARITY_LOC as CLARITY_LOC on CLARITY_TDL_TRAN.LOC_ID = CLARITY_LOC.LOC_ID
left outer join
dbo.mmp_PatientsTestaYes as mmp_PatientsTestaYes on CLARITY_TDL_TRAN.INT_PAT_ID = mmp_PatientsTestaYes.PAT_ID
left outer join
CLARITY.dbo.CLARITY_SA as CLARITY_SA on CLARITY_TDL_TRAN.SERV_AREA_ID = CLARITY_SA.SERV_AREA_ID
left outer join
CLARITY.dbo.CLARITY_SER as CLARITY_SER on CLARITY_TDL_TRAN.PERFORMING_PROV_ID = CLARITY_SER.PROV_ID
left outer join
CLARITY.dbo.CLARITY_EAF_TIN as CLARITY_EAF_TIN ON CLARITY_LOC.LOC_ID = CLARITY_EAF_TIN.FACILITY_ID
left outer join 
dbo.MMP_PPRVU as MMP_PPRVU on MMP_Staging_Units.eFinMod = MMP_PPRVU.MOD
left outer join 
dbo.MMP_revctrs$ as MMP_revctrs$ ON CLARITY_EAP.PROC_CODE = MMP_revctrs$.CPT
left outer join
MMP_PPRVU ON CLARITY_EAP_1.PROC_CODE = MMP_PPRVU.HCPCS
  WHERE        (CLARITY_TDL_TRAN.DETAIL_TYPE IN (1, 10)) AND       (mmp_PatientsTestaYes.PAT_ID IS NULL) AND (CLARITY_EAF_TIN.LINE = 1)

Answer 1

当在FROM子句中为表指定别名时，将别名命名为与表名相同的别名是非常困难的。

LEFT OUTER JOIN dbo.MMP_PPRVU AS MMP_PPRVU
    ON MMP_Staging_Units.eFinMod = MMP_PPRVU.MOD

此外，您在同一个表中加入了几个join，并且它的名称是相同的。这两个表中的一个表需要有一个与表名不同的别名。也许：

LEFT OUTER JOIN dbo.MMP_PPRVU as MMP_PPRVU_1
    ON MMP_Staging_Units.eFinMod = MMP_PPRVU.MOD

所以它与没有冲突

LEFT OUTER JOIN MMP_PPRVU
     ON CLARITY_EAP_1.PROC_CODE = MMP_PPRVU.HCPCS

我发现在编写FROM子句时使用稍微不同的缩进会很有帮助，这样这些东西就会更加突出，也更容易解析。

FROM CLARITY.dbo.CLARITY_TDL_TRAN AS CLARITY_TDL_TRAN
    LEFT OUTER JOIN dbo.MMP_STAGING_UNITS AS MMP_STAGING_UNITS
        ON MMP_Staging_Units.eTDL_ID = CLARITY_TDL_TRAN.TDL_ID
            AND CLARITY_TDL_TRAN.DETAIL_TYPE IN (
                1,
                10
                )
    LEFT OUTER JOIN CLARITY.dbo.CLARITY_EAP AS CLARITY_EAP
        ON CLARITY_EAP.PROC_ID = CLARITY_TDL_TRAN.PROC_ID
    LEFT OUTER JOIN CLARITY.dbo.CLARITY_EAP AS CLARITY_EAP1
        ON CLARITY_EAP1.PROC_ID = CLARITY_TDL_TRAN.PROC_ID --look into this alias
    LEFT OUTER JOIN CLARITY.dbo.CLARITY_POS AS CLARITY_POS
        ON CLARITY_TDL_TRAN.POS_ID = CLARITY_POS.POS_ID
    LEFT OUTER JOIN CLARITY.dbo.CLARITY_DEP AS CLARITY_DEP
        ON CLARITY_TDL_TRAN.DEPT_ID = CLARITY_DEP.DEPARTMENT_ID
    LEFT OUTER JOIN CLARITY.dbo.CLARITY_LOC AS CLARITY_LOC
        ON CLARITY_TDL_TRAN.LOC_ID = CLARITY_LOC.LOC_ID
    LEFT OUTER JOIN dbo.mmp_PatientsTestaYes AS mmp_PatientsTestaYes
        ON CLARITY_TDL_TRAN.INT_PAT_ID = mmp_PatientsTestaYes.PAT_ID
    LEFT OUTER JOIN CLARITY.dbo.CLARITY_SA AS CLARITY_SA
        ON CLARITY_TDL_TRAN.SERV_AREA_ID = CLARITY_SA.SERV_AREA_ID
    LEFT OUTER JOIN CLARITY.dbo.CLARITY_SER AS CLARITY_SER
        ON CLARITY_TDL_TRAN.PERFORMING_PROV_ID = CLARITY_SER.PROV_ID
    LEFT OUTER JOIN CLARITY.dbo.CLARITY_EAF_TIN AS CLARITY_EAF_TIN
        ON CLARITY_LOC.LOC_ID = CLARITY_EAF_TIN.FACILITY_ID
    LEFT OUTER JOIN dbo.MMP_PPRVU AS MMP_PPRVU_1
        ON MMP_Staging_Units.eFinMod = MMP_PPRVU.MOD
    LEFT OUTER JOIN dbo.MMP_revctrs$ AS MMP_revctrs$
        ON CLARITY_EAP.PROC_CODE = MMP_revctrs$.CPT
    LEFT OUTER JOIN MMP_PPRVU
        ON CLARITY_EAP_1.PROC_CODE = MMP_PPRVU.HCPCS

Answer 2

我喜欢使用缩进来帮助保持联接的可理解性。

您可以在下面看到，您为针对CLARITY_EAP的第二个联接提供了CLARITY_EAP1的别名，但当您尝试针对它进行联接时，您使用了未定义的别名CLARITY_EAP_1，该别名可能会引发错误。

SELECT........
FROM CLARITY.dbo.CLARITY_TDL_TRAN as CLARITY_TDL_TRAN  
    left outer join dbo.MMP_STAGING_UNITS as MMP_STAGING_UNITS on MMP_Staging_Units.eTDL_ID = CLARITY_TDL_TRAN.TDL_ID and CLARITY_TDL_TRAN.DETAIL_TYPE in (1, 10)
        left outer join dbo.MMP_PPRVU as MMP_PPRVU1 on MMP_Staging_Units.eFinMod = MMP_PPRVU.MOD
    left outer join CLARITY.dbo.CLARITY_EAP as CLARITY_EAP on CLARITY_EAP.PROC_ID = CLARITY_TDL_TRAN.PROC_ID
        left outer join dbo.MMP_revctrs$ as MMP_revctrs$ ON CLARITY_EAP.PROC_CODE = MMP_revctrs$.CPT
    left outer join CLARITY.dbo.CLARITY_EAP AS CLARITY_EAP1 on CLARITY_EAP1.PROC_ID = CLARITY_TDL_TRAN.PROC_ID --look into this alias
        left outer join MMP_PPRVU as MMP_PPRVU2 ON CLARITY_EAP1.PROC_CODE = MMP_PPRVU2.HCPCS
    left outer join CLARITY.dbo.CLARITY_POS as CLARITY_POS on CLARITY_TDL_TRAN.POS_ID = CLARITY_POS.POS_ID
    left outer join CLARITY.dbo.CLARITY_DEP as CLARITY_DEP on CLARITY_TDL_TRAN.DEPT_ID = CLARITY_DEP.DEPARTMENT_ID
    left outer join CLARITY.dbo.CLARITY_LOC as CLARITY_LOC on CLARITY_TDL_TRAN.LOC_ID = CLARITY_LOC.LOC_ID
        left outer join CLARITY.dbo.CLARITY_EAF_TIN as CLARITY_EAF_TIN ON CLARITY_LOC.LOC_ID = CLARITY_EAF_TIN.FACILITY_ID
    left outer join dbo.mmp_PatientsTestaYes as mmp_PatientsTestaYes on CLARITY_TDL_TRAN.INT_PAT_ID = mmp_PatientsTestaYes.PAT_ID
    left outer join CLARITY.dbo.CLARITY_SA as CLARITY_SA on CLARITY_TDL_TRAN.SERV_AREA_ID = CLARITY_SA.SERV_AREA_ID
    left outer join CLARITY.dbo.CLARITY_SER as CLARITY_SER on CLARITY_TDL_TRAN.PERFORMING_PROV_ID = CLARITY_SER.PROV_ID
WHERE
    CLARITY_TDL_TRAN.DETAIL_TYPE IN (1, 10) AND
    mmp_PatientsTestaYes.PAT_ID IS NULL AND
    CLARITY_EAF_TIN.LINE = 1