SQL Server联接和WHERE语句不工作

我要做的第一件事是在整个过程中使用连接的标准化:

SELECT 
    contacts.firstname , 
    contacts.lastname , 
    contacts.address1 , 
    contacts.city, 
    contacts.zip, 
    countries.title AS countrytitle, 
    states.title AS statetitle 
FROM providers
    INNER JOIN payableinvoices ON providers.providerid=payableinvoices.providerid 
    INNER JOIN contacts ON providers.contactid=contacts.contactid
    LEFT JOIN countries ON countries.countryid=contacts.countryid 
    LEFT JOIN states ON states.stateid=contacts.stateid 
WHERE payableinvoices.payableinvoiceid=4 

我觉得上面的内容不错。

如果您没有获得预期返回的行,则需要从基本的“Select…From Providers”开始,例如:

• 从基本的“从提供者中选择…”
• 将内部联接添加到PayableInvoices…测试
• 添加Where条件…测试 等

所以你很快就会发现排在哪里被排除了。

尝试使用:

SELECT 
    C.FirstName , 
    C.LastName , 
    C.Address1 , 
    C.City, 
    C.Zip, 
    CO.Title AS CountryTitle, 
    S.Title AS StateTitle 
FROM Contacts C
INNER JOIN Providers P ON P.ContactID = C.ContactID
INNER JOIN PayableInvoices PI ON PI.ContactID = P.ContactID
LEFT JOIN Countries CO ON CO.CountryID = C.CountryID 
LEFT JOIN States S ON S.StateID = C.StateID
WHERE PI.PayableInvoiceID = 4