How to get the root ID of a parent child structure

Question

Given a product ID, I need to find the top most category that product exists in.

There are 3 top categories, we'll call them A, B, and C.

I have a Categories table that holds all categories. This includes categories A, B, C as well as all subcategories such as foo, bar etc...

I also have a table called ProductCategories. This table contains a reference for each product and the category this product belongs to. If a product is categorised under Bar which is a subcategory of Foo which is a subcategory of B the structure is B -> Foo -> Bar the product in question would have 3 entries in the ProductCategories table.

This might explain it better:

Categories
+--------------------------------+
| ID | Name           | ParentID |
+----+----------------+----------+
| 1  | B              | Null     |
+----+----------------+----------+
| 2  | Foo            | 1        |
+----+----------------+----------+
| 3  | Bar            | 2        |
+----+----------------+----------+
| 4  | A              | Null     |
+----+----------------+----------+
| 5  | Subcategory    | 4        |
+----+----------------+----------+
| 6  | AnotherSubCat  | 5        |
+----+----------------+----------+
| 7  | SoManySubCats  | 6        |
+----+----------------+----------+

ProductCategories
+-----------+----------------+
| ProductID | ParentCategory |
+-----------+----------------+
| 50        | 2              |    // Product 50 would be:
+-----------+----------------+    // B -> Foo -> Bar
| 50        | 1              |
+-----------+----------------+
| 50        | 3              |
+-----------+----------------+ 
| 89        | 5              |    // Product 89 would be:
+-----------+----------------+    // A -> Subcategory -> AnotherSubCat -> SoManySubCats
| 89        | 4              |
+-----------+----------------+
| 89        | 7              |
+-----------+----------------+
| 89        | 6              |
+-----------+----------------+

There's nothing I can do about this database structure.

I can't figure out how to write a query where I can provide a product ID and it will tell me the top category, either A, B or C.

Hopefully someone can shed some light on this.

Oh yeah, I'm using MS Access 2003.

Answer 1

My query described in prose: join the product's categories with with the categories table and select the category, that has NULL as it's ParentID.

SELECT Categories.Name 
FROM Categories, ProductCategories 
WHERE ProductCategories.ParentCategory = Categories.ID 
  AND ProductCategories.ProductID = 50
  AND Categories.ParentID IS NULL