loop in stored procedure?

The thing to bear in mind here is that you should start from the bottom and
work up. That way, you won't be checking lots of paths, just the one that th
e
child is in.
If a child node can only exist in one place in the tree, then just use a
variable to hold the current parent, updating it each time with its parent.
Stop when you either get to the root, or the category you're looking for.
If a child node can exist in multiple categories, then use a temporary
table. Populate the table with all the parents of the current node, and then
populate it with the parents of the nodes in the temporary table (but only
the ones you haven't processed yet). Stop either when the populate function
doesn't put any more rows in, or else when you find the one you're after.
Rob
"Fabuloussites" wrote:

> I have a situation where there is one table with parent and child nodes
> i.e.
> catID, ParentID
> i would like to make a query to find out if an entry resides in a certain
> category.
> for instance, say i have
> Root
> --Category 1
> --Child 1A
> --Child 1B
> --Child 1C
> --Child 1Ca
>
> if i know the CatID for the Child 1Ca node, how can i write a query that i
s
> able to tell me that it's ultimtely a child of the root nde (Category 1)?
> Keep in mind that Category 1 is not always the root node in the tree.
>
>
>Whether you choose a stored procedure or a function depends on how you're
using it. If you have a piece of C# code which wants to find out if a
particular item is in a particular category, then use a stored procedure. If
you want to be able to use it in queries, then a function. Here's a function
which returns a bit (either 0 or 1) to indicate if an item is an ancestor in
the tree.
--First create the table and populate it with some samples.
create table fab_tree (parentid int, childid int)
insert into fab_tree values (0,1)
insert into fab_tree values (0,2)
insert into fab_tree values (0,3)
insert into fab_tree values (1,10)
insert into fab_tree values (1,11)
insert into fab_tree values (1,12)
insert into fab_tree values (2,20)
insert into fab_tree values (2,21)
insert into fab_tree values (2,22)
insert into fab_tree values (3,30)
insert into fab_tree values (3,31)
insert into fab_tree values (3,32)
insert into fab_tree values (30,300)
insert into fab_tree values (30,301)
insert into fab_tree values (30,302)
insert into fab_tree values (50,500)
go
create function [dbo].[isancestor](@.parentid int, @.childid int) returns bit as
begin
declare @.res bit
set @.res = 0
declare @.node int
set @.node = @.childid
--Keep looking until we get to the end, or the parent
while (@.node not in (0, @.parentid))
begin
select @.node = parentid
from fab_tree
where childid = @.node
--If the current child has no parent, then pretend we got to the top of
the tree
if (@.@.rowcount = 0)
set @.node = 0
end
--Check the success condition
if (@.node = @.parentid)
set @.res = 1
return @.res
end
go
select 1,10,dbo.isancestor(1,10)
select 1,20,dbo.isancestor(1,20)
select 3,300,dbo.isancestor(3,300)
select 5,500,dbo.isancestor(5,500)
select 9,700,dbo.isancestor(9,700)
But I'm guessing you probably want something that will find the category of
a particular item. In which case, how about :
create function dbo.getCategory(@.childid int) returns int as
begin
declare @.node int
set @.node = @.childid
declare @.nodeparent int
set @.nodeparent = @.node
--Keep looking until we get to the top of the tree
while (@.nodeparent > 0)
begin
--We need to now check the parent of the current parent
set @.node = @.nodeparent
select @.nodeparent = parentid
from fab_tree
where childid = @.node
--If the current child has no parent, then pretend we got to the top of
the tree
if (@.@.rowcount = 0)
begin
set @.node = 0
set @.nodeparent = 0
end
end
return @.node
end
go
select dbo.getcategory(childid), *
from fab_tree
That way, you can ask within a query for the category of an item in the tree
.
Hope this helps,
Rob
"Fabuloussites" wrote:
> Thanks for the comments. That's my thought process. If i were programmin
g
> in .net of c i would know how to do it :). In SQL, i'm a newbie. Could
you
> give me some direction? should i do it completely in a stored procedure o
f
> should i use a user defined function? IN my case, each node can only have
> one parent.
>
> "Rob Farley" wrote:
>|||Rob,
Thanks for all of the work. I really appreciate it. i'll update you once I
have some time to work with it.
"Rob Farley" wrote:
> Whether you choose a stored procedure or a function depends on how you're
> using it. If you have a piece of C# code which wants to find out if a
> particular item is in a particular category, then use a stored procedure.
If
> you want to be able to use it in queries, then a function. Here's a functi
on
> which returns a bit (either 0 or 1) to indicate if an item is an ancestor
in
> the tree.
> --First create the table and populate it with some samples.
> create table fab_tree (parentid int, childid int)
> insert into fab_tree values (0,1)
> insert into fab_tree values (0,2)
> insert into fab_tree values (0,3)
> insert into fab_tree values (1,10)
> insert into fab_tree values (1,11)
> insert into fab_tree values (1,12)
> insert into fab_tree values (2,20)
> insert into fab_tree values (2,21)
> insert into fab_tree values (2,22)
> insert into fab_tree values (3,30)
> insert into fab_tree values (3,31)
> insert into fab_tree values (3,32)
> insert into fab_tree values (30,300)
> insert into fab_tree values (30,301)
> insert into fab_tree values (30,302)
> insert into fab_tree values (50,500)
> go
> create function [dbo].[isancestor](@.parentid int, @.childid int) returns bit as
> begin
> declare @.res bit
> set @.res = 0
> declare @.node int
> set @.node = @.childid
> --Keep looking until we get to the end, or the parent
> while (@.node not in (0, @.parentid))
> begin
> select @.node = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> set @.node = 0
> end
> --Check the success condition
> if (@.node = @.parentid)
> set @.res = 1
> return @.res
> end
> go
> select 1,10,dbo.isancestor(1,10)
> select 1,20,dbo.isancestor(1,20)
> select 3,300,dbo.isancestor(3,300)
> select 5,500,dbo.isancestor(5,500)
> select 9,700,dbo.isancestor(9,700)
>
> But I'm guessing you probably want something that will find the category o
f
> a particular item. In which case, how about :
> create function dbo.getCategory(@.childid int) returns int as
> begin
> declare @.node int
> set @.node = @.childid
> declare @.nodeparent int
> set @.nodeparent = @.node
> --Keep looking until we get to the top of the tree
> while (@.nodeparent > 0)
> begin
> --We need to now check the parent of the current parent
> set @.node = @.nodeparent
> select @.nodeparent = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> begin
> set @.node = 0
> set @.nodeparent = 0
> end
> end
> return @.node
> end
> go
> select dbo.getcategory(childid), *
> from fab_tree
> That way, you can ask within a query for the category of an item in the tr
ee.
> Hope this helps,
> Rob
>
> "Fabuloussites" wrote:
>