OK, thanks for having a look earlier.
I was away at the weekend, and have since come back and tried it out, and
ON subcategories.SubCategoryID = Products.SubCategory_ID
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.