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:)