How to get the root ID of a parent child structure

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.

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

<pre>
Declare @Categories Table 
(
     ID int,[Name] varchar(100),ParentID int
)
insert into @Categories
Select 1,'B',Null UNION ALL
Select 2,'Foo',1  UNION ALL
Select 3,'Bar',2  UNION ALL
Select 4,'A',Null  UNION ALL
Select 5,'Subcategory',4  UNION ALL
Select 6,'AnotherSubCat',5  UNION ALL
Select 7,'SoManySubCats',6 

Declare @ProductCategories Table
(
    ProductID int,ParentCategory int
)
insert into @ProductCategories
Select  50,2  UNION ALL
Select  50,1  UNION ALL
Select  50,3  UNION ALL
Select  89,5  UNION ALL
Select  89,4 

;with CTE AS
(
    SELECT ID,[Name],ParentId,P.ProductID,
    Cast(ID As Varchar(Max)) As ReportingID,
    Cast([Name] As Varchar(Max)) As ReportingName
    From @Categories As C
    INNER JOIN @ProductCategories As P On C.ID=P.ParentCategory
    Where ParentId is null

    UNION ALL

    SELECT C.ID,C.[Name],C.ParentId,P.ProductID,
    Cast(CTE.ReportingID+'->'+Cast(C.ID As Varchar(10)) As Varchar(Max)) As ReportingID,
    Cast(CTE.ReportingName+'->'+C.[Name] As Varchar(Max)) As ReportingName
    From @Categories As C
    INNER JOIN @ProductCategories As P On C.ID=P.ParentCategory
    INNER JOIN CTE ON C.ParentId=CTE.ID 
)
Select *
From CTE

--now add where cluase to get the reportingname


</pre>