SQL Server joins and where statements not working

Question

I have this SQL Server statement, and I am not sure why it is returning no records:

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

Simply, I have the following tables:

• contacts table: its columns include contactid, firstname, lastname, address1, city, zip
• providers table: its columns include providerid, contactid
• payableinvoices table: its columns include payableinvoiceid, providerid

So it is simply linking the primary keys and foreign keys together to get the require fields that correspond to that "payableinvoiceid", which has a provider assigned to it.

Please help. Thanks!

Answer 1

Try using:

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 

Answer 2

The first thing I'd do is standardise to use joins throughout:

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 

The above looks fine to me.

If you're not getting the rows you expect back, you need to work your way up from the basic "SELECT...FROM providers" e.g.:

• start with a basic "SELECT....FROM providers"
• add the INNER JOIN to payableinvoices...test that
• add the WHERE condition...test that etc etc

So you'll soon find where the rows are being ruled out.