对“无法绑定多部分标识符”感到困惑SQL 2008 中的错误

Question

我创建了一个从多个表获取数据的视图，当我去编译它时，我不断收到“多部分标识符无法绑定”错误，这让我完全困惑于为什么。下面是我的 SQL 视图（错误消息来自粗体文本），请看一下并请帮助：

CREATE VIEW AMACO_VIEW_THIRD_PARTY_INSURANCE_INITIAL_LETTERS
AS
    SELECT
        CUSTOMERS.ID,
        CUSTOMERS.FIRST_NAME,
        CUSTOMERS.LAST_NAME,
        CUSTOMER_ACCIDENT_DTLS.ACCIDENT_DATE,
        CUSTOMER_ACCIDENT_DTLS.ACCIDENT_TIME,
        HIRED_VEHICLES.HIRE_CHARGE_AMT,
        CUSTOMER_STORAGE.START_DATE,
        CUSTOMER_STORAGE.STORAGE_FEE_PER_DAY,
        CUSTOMER_RECOVERY.RECOVERY_FEE,
        THIRD_PARTIES.THIRD_PARTY_NAME,
        THIRD_PARTY_VEHICLES.VEHICLE_REGISTRATION,
        INSURANCE_COMPANIES.INSURANCE_COMPANY_NAME,
        ADDRESSES.STREET_1,
        ADDRESSES.STREET_2,
        CITIES.CITY_NAME,
        DISTRICTS.DISTRICT_NAME,
        COUNTRIES.COUNTRY_NAME,
        ADDRESSES.POSTAL_CODE
    FROM CUSTOMERS, THIRD_PARTY_INITIAL_LETTER_PRINTED
    LEFT JOIN CUSTOMER_ACCIDENT_HDRS
        ON **CUSTOMERS.ID** = CUSTOMER_ACCIDENT_HDRS.CUSTOMER_ID
    INNER JOIN CUSTOMER_ACCIDENT_DTLS
        ON CUSTOMER_ACCIDENT_HDRS.ID = CUSTOMER_ACCIDENT_DTLS.CUSTOMER_ACCIDENT_HDR_ID
    LEFT JOIN CUSTOMER_VEHICLES
        ON **CUSTOMERS.CUSTOMER_VEHICLE_ID** = CUSTOMER_VEHICLES.ID
    INNER JOIN THIRD_PARTIES
        ON THIRD_PARTIES.CUSTOMER_ACCIDENT_ID = CUSTOMER_ACCIDENT_HDRS.ID
    INNER JOIN THIRD_PARTY_VEHICLES
        ON THIRD_PARTIES.THIRD_PARTY_VEHICLE_ID = THIRD_PARTY_VEHICLES.ID
    INNER JOIN THIRD_PARTY_INSURANCE_POLICIES
        ON THIRD_PARTIES.ID = THIRD_PARTY_INSURANCE_POLICIES.THIRD_PARTY_ID
    INNER JOIN INSURANCE_POLICIES
        ON THIRD_PARTY_INSURANCE_POLICIES.INSURANCE_POLICY_ID = INSURANCE_POLICIES.ID
    INNER JOIN INSURANCE_COMPANIES
        ON INSURANCE_POLICIES.INSURANCE_COMPANY_ID = INSURANCE_COMPANIES.ID
    LEFT JOIN HIRED_VEHICLES
        ON **CUSTOMERS.ID** = HIRED_VEHICLES.CUSTOMER_ID
    INNER JOIN CUSTOMER_STORAGE
        ON CUSTOMER_VEHICLES.ID = CUSTOMER_STORAGE.CUSTOMER_VEHICLE_ID
    INNER JOIN CUSTOMER_RECOVERY
        ON CUSTOMER_VEHICLES.ID = CUSTOMER_RECOVERY.CUSTOMER_VEHICLE_ID
    INNER JOIN ADDRESSES
        ON INSURANCE_COMPANIES.ADDRESS_ID = ADDRESSES.ID
    INNER JOIN COUNTRIES
        ON ADDRESSES.COUNTRY_ID = COUNTRIES.ID
    INNER JOIN DISTRICTS
        ON ADDRESSES.DISTRICT_ID = DISTRICTS.ID
    INNER JOIN CITIES
        ON ADDRESSES.CITY_ID = CITIES.ID
    WHERE CUSTOMER_ACCIDENT_HDRS.ID NOT IN (SELECT THIRD_PARTY_INITIAL_LETTER_PRINTED.CUSTOMER_ACCIDENT_HDR_ID FROM THIRD_PARTY_INITIAL_LETTER_PRINTED)
GO

Answer 1

部分

, THIRD_PARTY_INITIAL_LETTER_PRINTED

删除“查看查询的简化版本”

DECLARE @Table1 TABLE(
        ID INT
)
DECLARE @Table2 TABLE(
        ID INT
)
DECLARE @Table3 TABLE(
        ID INT
)

SELECT  *
FROM    @Table1 t1,
        @Table2 t2 LEFT JOIN
        @Table3 t3  ON  t1.ID = t3.ID

这将产生相同的错误。

如果您希望CROSS JOIN t1 和t2，则必须在使用其余连接之前执行此操作。

像这样的东西

;WITH Vals AS (
        SELECT  t1.ID t1ID,
                t2.ID t2ID
        FROM    @Table1 t1,
                @Table2 t2
)
SELECT  *
FROM    Vals v LEFT JOIN
        @Table3 t3  ON  v.t1ID = t3.ID

Answer 2

好吧，问题出在 THIRD_PARTY_INITIAL_LETTER_PRINTED 上，但问题是当我将它插入到外部 SELECT 语句中时，而不是子查询语句中。