Can this be done?

[kt366]

I have a table with categories in and would like to return a result set which is in the order I want to display them in. Currently I return all the categories of level 1, then as I iterate through them in the application, I check to see if they have a sub category (level 2) and then return to the database and return the sub categories for that category, and then iterate through the sub category records and look to see if any have a level 3 subsubcategory. This seems a very lengthy process with unecessary trips to the database, but I can't figure out how to return through one query a dataset in the order of my menu.
Please Help. Thanks

 

[bborissov]

What version of SQL Server? In SQL Server 2005 you can have recursive queries. But I never tryed them till now.
I have the same situation when I want to build a TreeView in but all is in my frontend. I have a SP where i pass wich a Parent as a parameter and it returns me only these records that have a parent key I passed. Of course that maybe is not your situation

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[r937]

there are two common ways to store hierarchical data -- the adjacency model (in which each node carries the "parent id") and the nested set model (which uses "lft" and "rgt" pointers)

in the adjacency model, returning all subcategories can be done in one query if there is an absolute limit to the number of levels of subcategories, or a limit to how "deep" you want to go

in the nested set model, it's trivial (or so i'm told -- i don't use the nested set model)

r937.com | rudy.ca

 

[kt366]

I am using SQL Server 2000, sounds a similar situation, I have found a way of returning the top category level with the subcategory level all in the correct order, but I am having a nightmare getting level 3 into the correct order within the query.

 

[kt366]

I know that there can only ever be three level. Do you know where I can find out more about the adjacency model? Thanks

 

[vongrunt]

I'm using nested-set (NSM) model for several purposes. It makes queries extremely fast (assuming lft pointer is indexed). The only problem is cost of updating pointers whenever tree structure is changed (node added/deleted/moved). If tree structure is relatively static then I'd say it is acceptable.

There are also other alternatives, like nested intervals, materialized paths etc.

That said, I made fast routine for NSM indexing (calculation of pointer values) and will probably FAQ it next week.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[r937]

kt366, here's the query for three levels --

select cat.name            as catlevel1 
     , subcat.name         as catlevel2 
     , subsubcat.name      as catlevel3 
  from categories as cat 
left outer
  join categories as subcat 
    on subcat.parentid = cat.id
left outer
  join categories as subsubcat 
    on subsubcat.parentid = subcat.id
order
    by cat.seq         
     , subcat.seq       
     , subsubcat.seq

r937.com | rudy.ca

 

[kt366]

Thanks a million for that, that is perfect.