So from that the idea is to join it up so that I can have a query that displays all the Products with a particular Category and SubCategory, but I’m unsure of the syntax to join the main table up to both other tables?
your tbl_Products table table has two separate foreign key links, one to the tbl_Categories table table and one to the tbl_SubCategories table table
this is extraneous information in the tbl_Products table table, since your 2nd post indicates that the tbl_SubCategories table table links to the tbl_Categories table table
so your tbl_Products table table should really only link to the tbl_SubCategories table table
then your query would look like this –
SELECT ...
FROM tbl_Products
INNER
JOIN tbl_SubCategories
ON tbl_SubCategories.SubCategory_ID = tbl_Products.SubCategory_ID
INNER
JOIN tbl_Categories
ON tbl_Categories.Category_ID = tbl_SubCategories.CategeryLinkID
I was away at the weekend, and have since come back and tried it out, and
SELECT *
FROM Products
INNER
JOIN subcategories
ON subcategories.SubCategoryID = Products.SubCategory_ID
INNER
JOIN categories
ON categories.category_ID = subcategories.CategoryLinkID
WHERE SubCategory_ID = 3
seems to be doing what I had in mind.
With the example you mention with subcategory 23 & category B, then it should work as long as the user enters the categories and subcategories correctly in the first place. (I’m using dependent drop downs on the add product page, so that the subcategory list only displays the subcategories relevant to the category selected in its drop down list.
There are some categories that are not split into subcategories - in those cases I have simply created a single catch all sub category for them.
actually no, that can’t be right, as SubCategories.SubCategory_ID is the ID which needs to determine which records are displayed, and should show the fields from the main Products table, along with the Category from the Categories table, and SubCategory from the SubCategories table.
I assume I need to have a second recordset to select the category ID to start from and match in the join.
Would that just be to select from the Products table where, for example, CategoryID = 1, then the join query would look up the matching records via the other tables?