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/

How to Query a nested Hierarchy in TSQL

All:
I have a table called Users and there is a column called ParentID that
either contains 0 or another valid userID. This is what I want to do: When I
have a userID, I want to retrieve all UserIDs until ParentID for a searched
user is 0.
Script to simulate the environment:
create table users
(userID Int,
parentID Int)
insert into users
select 1, 0
union all
select 2, 1
union all
select 3, 2
union all
select 4, 3
union all
select 5, 4
union all
select 6, 4
union all
select 7, 5
union all
select 8, 6
union all
select 9, 7
union all
select 10, 8
Situation: I will be supplied with a specific user ID and I need to retun
all userIDs in comma delimited format until ParentID becomes 0 in the
hierarchy.
Example:
UserID 1 should return only one row as the ParentID is 0 for userID 1
UserID 8 should return following userIDs
6,4, 3, 2, 1
I hope everything is clear.This article may give you some idea.
http://www.windowsitpro.com/Article...5715/15715.html
Regards
Mark wrote:
> All:
> I have a table called Users and there is a column called ParentID that
> either contains 0 or another valid userID. This is what I want to do: When
I
> have a userID, I want to retrieve all UserIDs until ParentID for a searche
d
> user is 0.
> Script to simulate the environment:
> create table users
> (userID Int,
> parentID Int)
> insert into users
> select 1, 0
> union all
> select 2, 1
> union all
> select 3, 2
> union all
> select 4, 3
> union all
> select 5, 4
> union all
> select 6, 4
> union all
> select 7, 5
> union all
> select 8, 6
> union all
> select 9, 7
> union all
> select 10, 8
> Situation: I will be supplied with a specific user ID and I need to retun
> all userIDs in comma delimited format until ParentID becomes 0 in the
> hierarchy.
> Example:
> UserID 1 should return only one row as the ParentID is 0 for userID 1
> UserID 8 should return following userIDs
> 6,4, 3, 2, 1
> I hope everything is clear.|||This article may give you some idea.
http://www.windowsitpro.com/Article...5715/15715.html
Regards
Mark wrote:
> All:
> I have a table called Users and there is a column called ParentID that
> either contains 0 or another valid userID. This is what I want to do: When
I
> have a userID, I want to retrieve all UserIDs until ParentID for a searche
d
> user is 0.
> Script to simulate the environment:
> create table users
> (userID Int,
> parentID Int)
> insert into users
> select 1, 0
> union all
> select 2, 1
> union all
> select 3, 2
> union all
> select 4, 3
> union all
> select 5, 4
> union all
> select 6, 4
> union all
> select 7, 5
> union all
> select 8, 6
> union all
> select 9, 7
> union all
> select 10, 8
> Situation: I will be supplied with a specific user ID and I need to retun
> all userIDs in comma delimited format until ParentID becomes 0 in the
> hierarchy.
> Example:
> UserID 1 should return only one row as the ParentID is 0 for userID 1
> UserID 8 should return following userIDs
> 6,4, 3, 2, 1
> I hope everything is clear.|||Mark,
Try this function:
create function dbo.fn_getpath(@.uid as int) returns varchar(8000)
as
begin
declare @.path as varchar(8000);
set @.path = cast(@.uid as varchar(10));
while @.uid <> 0
begin
set @.uid = (select parentid from users where userid = @.uid);
set @.path = @.path + ',' + cast(@.uid as varchar(10));
end
return @.path;
end
go
select dbo.fn_getpath(5)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:1C6A7C66-191E-41A7-AD3C-33E84232CCF6@.microsoft.com...
> All:
> I have a table called Users and there is a column called ParentID that
> either contains 0 or another valid userID. This is what I want to do: When
> I
> have a userID, I want to retrieve all UserIDs until ParentID for a
> searched
> user is 0.
> Script to simulate the environment:
> create table users
> (userID Int,
> parentID Int)
> insert into users
> select 1, 0
> union all
> select 2, 1
> union all
> select 3, 2
> union all
> select 4, 3
> union all
> select 5, 4
> union all
> select 6, 4
> union all
> select 7, 5
> union all
> select 8, 6
> union all
> select 9, 7
> union all
> select 10, 8
> Situation: I will be supplied with a specific user ID and I need to retun
> all userIDs in comma delimited format until ParentID becomes 0 in the
> hierarchy.
> Example:
> UserID 1 should return only one row as the ParentID is 0 for userID 1
> UserID 8 should return following userIDs
> 6,4, 3, 2, 1
> I hope everything is clear.
>|||Look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML
http://milambda.blogspot.com/|||"Muhammad Akhter" <mnadeemakhter@.gmail.com> wrote in message
news:1134511951.052763.116790@.g44g2000cwa.googlegroups.com...
> This article may give you some idea.
> http://www.windowsitpro.com/Article...5715/15715.html
Lets just count the *years* that this kind of approach is the only one
available:(:)|||Get a copy of TREES & HIERARCHIES IN SQL for several approaches to this
problem.
Why do you wish to destroy First Normal Form (1NF)? It is the
foundation of RDBMS, after all.
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134875755.271066.18350@.g44g2000cwa.googlegroups.com...
> Get a copy of TREES & HIERARCHIES IN SQL for several approaches to this
> problem.
Good luck with the book...although some may wonder why
it still is appropriate in the 21st century:)