how to query the using recursive?

Hello,

I have the following tables:

Article(articleID,CategoryID,ArticleTitle)Categories(categoryID,ParentID,CategoryTitle)

I am trying to retrieve themain category ID for a specific article ID.

For example lets say I have this data:

Article:

1, 10 , "some title"2,10,"some title"3,11,"some title"

Categories:

1, NULL , "some title"2, 1, "some title"10, 2, "some title"11, 10 , "some title"

In this example I want to know who is the main category of article 3.

The query should return the answer:1

Thats because:

The article ID 3 is inside category 11.Parent for category 11 is 10.Parent for category 10 is 2. Parent for category 2 is 1and Parent for category 1 is NULL, which means category 1 has no parents and it is the main category.Query will return article id, category id, main_category_id, ArticleTitle, CategoryTitle (some union between 2 tables)

Do you have any suggestions for such query?

Thanks all.

check this post for a non-recursive technique for storing and traversing hierarchical data in sql:http://forums.asp.net/p/1084407/1611195.aspx#1611195

the technique is more fully described here:http://www.developerfusion.co.uk/show/4633/2/