Search results

I've been tinkering a lot with the JOIN options, but INNER JOIN won't return the correct results in all cases. Given the query we started out with, indeed INNER JOIN would work just as well because the crtiteria is on the outermost table. However, suppose the user entered only a criteria for...

Replying to LPurvis... Actually the criteria is very dynamic and can be composed of up to 10 criteria referencing fields in either one of the 3 tables (all of those fields are indexed). The query I presented is one of the simplest ones, using a single criteria. Also, the SELECT list in reality...

In fact I simplified the query a little too much to make my point, because I also need data from the ProdSuppls table. SELECT [Products].ProductID, [ProdSuppls].SupplierID, [Suppliers].Name FROM [Products] LEFT JOIN ([ProdSuppls] LEFT JOIN [Suppliers] ON [ProdSuppls].SupplierID =...

Yes, both of those fields are indexed. I also just compacted the database in order to eliminate slowness from that side. Interestingly enough, when changing both JOIN types to INNER JOIN it was lightning fast. However, the fact that I got the same 7 records as a result is just a coincidence. It...

I have a query that uses several LEFT JOINs to gather all its data from various lookup tables. However, oneof the criteria fed into that query is performing extremely poorly. Setup: [Products] with ProductID as PK, has 50000 records [ProdSuppls] with ProductID+SupplierID as PK, has 80000...