Help with join query syntax - one table to two others

Databases
1

Hope someone can help with this.

My db structure is:

tbl_Products

Product_ID
Product
etc
Category_ID
SubCategory_ID

tbl_Categories

Category_ID
Category

tbl_SubCategories

SubCategory_ID
SubCategory

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?

Any pointers much appreciated.

2

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
3

Actually, I also have a field CategeryLinkID in my SubCategories table, so would it be something like:

Products >> JOIN >> Categories >> JOIN >> SubCategories

4

OK, thanks for having a look earlier.

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.

5

not sure i can help you, because i’m not sure i fully understand the nuances of your table design

there are anomalies

for instance, if a product links to subcategory 23 in category B, what happens if subcategory 23 doesn’t actually belong to category B?

that’s why i said earlier that your tbl_Products table table should really only link to the tbl_SubCategories table table

now, if you have some products that only belong to a category, but not to a subcategory, that’s another anomaly that needs to be addresses…

6

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.

7

so more like:


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
WHERE tbl_SubCategories.SubCategory = 1

?

8

OK - so just:



SELECT ...
  FROM tbl_Products WHERE Category_ID = 1
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
9

you would just ad that as a WHERE clause in the query.

10

Thanks - that makes sense.

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?