Entire Category Structure with one query?

[webmigit]

I have a category structure like this...

ID  Name   Parent
1   Home   0
2   CPU    1
3   Intel  2
4   Memory 1
5   AMD    2
6   SDRAM  4

Many of you have done this sort of thing in different RAD languages.. currently I use a query to for each parent category to get the child categories.. (lots and lots of queries)...

Is there anyway to do it with one? So that I can conform the output to something like..

Home
  CPU
    Intel
    AMD
  Memory
    SDRAM

Any ideas anyone? I mean.. Attempts I take at this loop the server way too many times and I still haven't gotten anything like the results I want...

Tony ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[mannuleo]

hi there
i dont think there is a way to find out that result in a single query, but u can make a recursive stored procedure for that and passing an ID to that sp and in that sp u will declare a cursor for those records which have their parent category as that passed ID and then insert those IDs into a temp table and use that.

Enjoy...

 

[Ravi29]

hi!mannuleo

is it possible to use the stored-procedure in recurive method?. please give me one example with code.

with regards
Ravichandran.v

 

[MeanGreen]

Here is one way to use recursive loops to get what you want:
set nocount on
declare @output varchar(5000)
declare @homeparent integer, @homeid integer
select @output = ''
declare home_loop CURSOR for
select id,parent from <table> where parent = 0
open home_loop
fetch next from home_loop into @homeid,@homeparent
while @@fetch_status = 0
begin
select @output = @output + name + char(13)+char(10) from #test where id = @homeid
declare @lvl1parent integer,@lvl1id integer
declare lvl1_loop cursor for
select id,parent from <table> where parent = @homeid
open lvl1_loop
fetch next from lvl1_loop into @lvl1id,@lvl1parent
while @@fetch_status = 0
begin
select @output = @output + ' ' + name + char(13)+char(10) from #test where id = @lvl1id
declare @lvl2parent integer,@lvl2id integer
declare lvl2_loop cursor for
select id,parent from <table> where parent = @lvl1id
open lvl2_loop
fetch next from lvl2_loop into @lvl2id,@lvl2parent
while @@fetch_status = 0
begin
select @output = @output + ' ' + name + char(13)+char(10) from #test where id = @lvl2id
fetch next from lvl2_loop into @lvl2id,@lvl2parent
end
close lvl2_loop
deallocate lvl2_loop
fetch next from lvl1_loop into @lvl1id,@lvl1parent
end
close lvl1_loop
deallocate lvl1_loop
fetch next from home_loop into @homeid,@homeparent
end
close home_loop
deallocate home_loop
select @output


Hope this helps.

 

[TerryStover]

Re: Recursive Loops

The @@fetch_status = 0 is global to the server. If you nest the loops you need to store the results of each fetch in a separate local variable.


instead of:
fetch next from lvl1_loop into @lvl1id,@lvl1parent
while @@fetch_status = 0

Use
declare @lvl1 smallint
fetch next from lvl1_loop into @lvl1id,@lvl1parent
set @lvl1= @@fetch_status
while @lvl1 = 0

 

[MeanGreen]

I ran this query myself on test data and it worked fine. I think I understand what your are referring to, but this query worked fine within my test environment. For what condition would this query not work? Can you give me sample data that would fail based on the original request?

Thank you.