ex-webby

Hi All

I have a table called tblCategory

It has 3 Fields :
CategoryID
ParentCatID
Name

The theory is, everything with a ParentCategoryID of 1 is a child of categoryid 1, so you can effectively build up a limitless tree of categories. If ParentCatID = 0, it is a master category.

My first (and the most important problem) is to return a list of all categories under 1 tree route...

so.. if I had the following records :

1,0,"Top Cat"
2,0,"2nd Top Cat"
3,1,"Child To Top Cat"
4,1,"2nd Child To Top Cat"
5,3,"Child to Child To Top Cat"

the tree should look like this (CategoryID only) :

1
|
|-3
| |
| |-5
|
|-4
|
2

I want to specify in a query where CategoryID= 1, and it returns me the records 1,3,5 and 4 (as they are all on the same branch as category ID 1. If you specified where CategoryID = 3, you would just get 3 and 5. Does that make sense?

I'm sure this is possible, and it's just that my feeble brain can't cope with it.


The second problem, but this is in no way as urgent as the above ...

is to return all of the records in order (for ease of placing them on the screen)...

so with the above example they would return in the order 1,3,5,4,2

Huge thanks in advance!

Simon

boxst

Hello

I know what you are trying to do, but not if it's possible in one SQL statement!

I have done something similiar with .JSP and .ASP by writing a recursive function that calls a single SQL statement.

Steve.

Andrewza

I can't see you doing it in one SQL statement, you'll have to have some recursive function that starts looking for ParentCatID = 0 ones, then recurses down the tree, which basically means one query for each entry.

ex-webby

Thanks for the reply.

The main problem I have, is that I need to return a list of records in another table that match the category id's in any of the returned categories, and then sort them. So it's a bit of a sod.

I suppose as a last resort, I could build a new temporary table first and then display the results of that.

cheers

Simon

dharbige

Simon,

I don't know what database you are using, but the following stored procedure should work in MSSQL (Note: I haven't tested it fully, but it seems to work with your sample data):

CREATE PROCEDURE ListNodes
(
@rootID as int,
@nest as int = 0
)
AS
set nocount on

declare @message varchar(100),
@cat_id int,
@parent_id int,
@name varchar(50),
@execstring varchar(30)

--
-- Declare output table if at level 0
--
if @nest = 0
begin
create table #templist (CategoryID int, ParentCategory int, [name] varchar(50))
end

select @cat_id = CategoryID, @parent_id = ParentCategory, @name = [name]
from SimonsTable where CategoryID = @rootID

select @message = str(@cat_id) + ', ' + str(@parent_id) + ', ' + @name
insert into #templist values (@cat_id, @parent_id, @name)
print @message

declare sub_nodes cursor local for
select CategoryID from SimonsTable where ParentCategory = @rootID

open sub_nodes
fetch next from sub_nodes INTO @cat_id

while @@fetch_status = 0
BEGIN
select @execstring = 'exec ListNodes ' + str(@cat_id, 10, 0) + ',' + str((@nest + 1), 2,0)
exec (@execstring)
fetch next from sub_nodes
INTO @cat_id
END

close sub_nodes
deallocate sub_nodes

if @nest = 0
begin
select * from #templist
drop table #templist
end
GO

PaulT00

Which database is this running on? If it's in Oracle then you could use the proprietary extension CONNECT BY PRIOR / START WITH which will let you do this in one SELECT statement. I don't believe there is a standard SQL equivalent though.

I've done this lots of times in e.g. ASP, using a recursive subroutine which constructs the SQL to open the children of a given node on the fly, based on a parent id passed in as a parameter.