That would be the SQL for your main recordset if you have relational tables. And the one that will be searched by the dropdowns and return products or items based on the filter.
What do you have for tables now?
If your main product table's recordset looks something like this;
SELECT *
FROM tblFoods
which is just super basic, but you get the idea.
Then it's returning everything "*" from the table called "tblFoods". But if this table has no reference to what brand or class it's in, then the search has no way of returning just those records. So you create a JOIN that relates all the tables involved into a joined recordset.
In the example SQL, it basically says;
SELECT * (Return all records)
FROM tblFoods (from the tblFoods table)
LEFT JOIN relFoods_Brands ON tblFoods.ID = relFoods_Brands.Parent_ID (join into this recordset the "relational" table called "relFoods_Brands" where the "ID" of the first table "tblFoods" matches the "Parent_ID" column of the "relFoods_Brands" table)
LEFT JOIN tblBrands ON tblFoods.BrandID = tblBrands.BrandsID (also join into this recordset the table called "tblBrands" where the "BrandID" column of the first table "tblFoods" matches the "BrandID" of the "tblBrands" table column)
LEFT JOIN tblClass ON relFoods_Brands.Parent_ClassID = tblClass.ClassID (finally, join into this recordset the table called "tblClass" where the "ClassID" column of the table "tblClass" matches the "Parent_ClassID" column of the relational table "relFoods_Brands)
I might have missed something, but I think this is correct. Don't get discouraged if it looks confusing. I still get very confused when writing these and I click the "Test" button a lot until I narrow it down to what I am shooting for.
TroyD
