I am writing a query that left joins a bunch of tables to Customer.

I am finding that when I get to the fifth table that all previsous joins stop working. The query still runs but the left joined table values show up as null or empty space. So all the customer records show up, but the left join tables that worked with 4 tables are now all empty with 5 tables.

The follwoing query works properly and returns values for all left outer joined tables:

SELECT Customer.ListID, Customer.TimeCreated, Customer.TimeModified, Customer.EditSequence, Customer.FullName, Customer.CompanyName, Customer.BillAddressAddr1, Customer.BillAddressAddr2, Customer.BillAddressAddr3, Customer.BillAddressAddr4, Customer.BillAddressCity, Customer.BillAddressState,Customer.BillAddressPostalCode, Customer.BillAddressCountry, Customer.Phone, Customer.Fax, Customer.Email,Customer.Contact, Customer.TermsRefListID, Terms.StdDiscountPct, Terms.StdDiscountDays, Terms.StdDueDays,Customer.Balance, Customer.SalesTaxCodeRefListID, ItemSalesTax.TaxRate, Customer.Notes, Customer.SalesRepRefListID, SalesRep.SalesRepEntityRefFullName, Customer.JobTypeRefFullName FROM {oj Customer LEFT OUTER JOIN SalesRep  LEFT OUTER JOIN Terms LEFT OUTER JOIN ItemSalesTax  ON (Customer.SalesRepRefListID = SalesRep.ListID)  on (Customer.TermsRefListID = Terms.ListID) on (Customer.ItemSalesTaxRefListID = ItemSalesTax.ListID) }

The follwoing query does not return values for the left outer joined tables, it is essentially the same query with one more table added.

SELECT Customer.ListID, Customer.TimeCreated, Customer.TimeModified, Customer.EditSequence, Customer.FullName, Customer.CompanyName, Customer.BillAddressAddr1, Customer.BillAddressAddr2, Customer.BillAddressAddr3, Customer.BillAddressAddr4, Customer.BillAddressCity, Customer.BillAddressState,Customer.BillAddressPostalCode, Customer.BillAddressCountry, Customer.Phone, Customer.Fax, Customer.Email,Customer.Contact, Customer.TermsRefListID, Terms.StdDiscountPct, Terms.StdDiscountDays, Terms.StdDueDays,Customer.Balance, Customer.SalesTaxCodeRefListID, ItemSalesTax.TaxRate, Customer.Notes, Customer.SalesRepRefListID, SalesRep.SalesRepEntityRefFullName, Customer.JobTypeRefFullName FROM {oj Customer LEFT OUTER JOIN SalesRep  LEFT OUTER JOIN Terms LEFT OUTER JOIN ItemSalesTax left outer join JobType  ON (Customer.SalesRepRefListID = SalesRep.ListID)  on (Customer.TermsRefListID = Terms.ListID) on (Customer.ItemSalesTaxRefListID = ItemSalesTax.ListID) on (Customer.JobTypeRefListID = JobType.ListID) }

Also, I was looking at the online documentation specifically the relationships. Is the following relationship a typo?

Customer.ItemSalesTaxRefListID related to ItemSaleCode

shouldn't it be this:

Customer.ItemSalesTaxRefListID related to ItemSalesTax

First I need to say to everyone reading this that QODBC sends XML requests to QuickBooks and it sends back a big XML document for us to sort out. There's no direct call to the QuickBooks DBMS, and while QODBC does a good job of creating virtual tables so that we can work with QuickBooks as if there were 115 tables, there's actual only one companyfilename.qbw file.

You should limit your joins to four tables maximum. In your second example only thing SalesRep doesn't already have in the Customer table are the Initial and Entity ListID details, so I've removed the SalesRep table reference so that I can include JobType instead (which is of greater value) like this:

SELECT Customer.ListID, Customer.TimeCreated, Customer.TimeModified, Customer.EditSequence,
Customer.FullName, Customer.CompanyName, Customer.BillAddressAddr1, Customer.BillAddressAddr2,
Customer.BillAddressAddr3, Customer.BillAddressAddr4, Customer.BillAddressCity,
Customer.BillAddressState,Customer.BillAddressPostalCode, Customer.BillAddressCountry,
Customer.Phone, Customer.Fax, Customer.Email,Customer.Contact, Customer.TermsRefListID,
Terms.StdDiscountPct, Terms.StdDiscountDays, Terms.StdDueDays,Customer.Balance,
Customer.SalesTaxCodeRefListID, ItemSalesTax.TaxRate, Customer.Notes, Customer.SalesRepRefListID,
Customer.SalesRepRefFullName, Customer.JobTypeRefFullName
FROM {oj Customer
LEFT OUTER JOIN Terms
LEFT OUTER JOIN ItemSalesTax
LEFT OUTER JOIN JobType   
on (Customer.TermsRefListID = Terms.ListID)
on (Customer.ItemSalesTaxRefListID = ItemSalesTax.ListID)
on (Customer.JobTypeRefListID = JobType.ListID) }

But this query runs very slowly.

In QODBC v6.5 and 7 we are looking to allowing you to do direct pass-through queries to the SQL engine under the QODBC Optimizer which will not only allow more complex queries to run, but it will work a lot faster than the single table mode used by QODBC v6.