Solved

Hierarchical Recordset HELP!

Posted on 2006-11-24
5
482 Views
Last Modified: 2008-02-01
Hi!  I am inexperienced with creating hierarchical recordsets and need some help with creating the SQL statement.

I need to display a product catalog in ASP.  My db structure (simplified) is below:

dt_CATEGORIES
....CATEGORY_ID
....CATDESCRIPTION
....PARENTCATEGORYID

dt_PRODCAT (Used to map multiple categories and products)
....ID
....CATEGORY_ID
....PRODUCT_ID

dt_PRODUCTS
....PRODUCT_ID
....PRODUCTNAME

I am having trouble getting my head around the subcategories.  With this structure it is possible to have an infinite number of category nestings, through the use of the PARENTCATEGORY_ID field that points back to the dt_CATEGORY table.

Can someone please provide me with code to create the ADO hierarchical recordset, and code to display it also please?

TIA...

TheFoot
0
Comment
Question by:Barry Jones
  • 2
  • 2
5 Comments
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 250 total points
ID: 18010545
you need to have a function for this
for example

create function root_category(@catid int)
returns int
as
begin
@pcatid  int
select @pcatid = parent_category_id from dt_CATEGORIES where CATEGORY_ID = @catid
while @pcatid is not null
begin
set @catid  = @pcatid
set @pcatid = select dbo.root_category(@pcatid )
end
return @catid end
===========================


then this statement will give you the root parent cateogry for all categories

select category_id, Catedescription, dbo.root_category(category_id,) from dt_CATEGORIES
0
 
LVL 2

Accepted Solution

by:
AtulKamdar earned 250 total points
ID: 18011143
HI,

THIS WILL HELP U:

Select CAT.CATEGORY_ID NODE, CAT.CATDESCRIPTION NODENAME, CAT.PARENTCATEGORYID ROOTID,
IsNull(PCAT.CATDESCRIPTION,'Root') ROOTNAME
From dt_CATEGORIES CAT Left Outer Join dt_CATEGORIES PCAT
On CAT.PARENTCATEGORYID = PCAT.CATEGORY_ID
UNION
SELECT PRO.PRODUCT_ID NODE, PRO.PRODUCTNAME NODENAME,
CPRO.CATEGORY_ID ROOTID, CAT.CNAME ROOTNAME
FROM dt_PRODUCTS PRO INNER JOIN dt_PRODCAT CPRO
ON PRO.PRODUCT_ID = CPRO.PRODUCT_ID
INNER JOIN  dt_CATEGORIES CAT ON CPRO.CATEGORY_ID = CAT.CATEGORY_ID
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 18011151
hi TheFoot ,
What version of sql you have 2000 or 2005?
0
 
LVL 12

Author Comment

by:Barry Jones
ID: 18019432
Thanks for your comments.

imran_fast: I use SQL 2000.  

Thanks for both of these solutions, they both work well and return the information that I need, but they do not create hierarchical or nested recordsets in ADO.  I need this to be able to "plug" the results into a third party ASP treeview control.

I have heard that the SHAPE and COMPUTE commands create the data in a structure that ADO recognises and creates nested recordsets.  Can anybody give me pointers on how to use these commands?  I am working through the help files, but am not successful yet.

Thanks, TheFoot
0
 
LVL 12

Author Comment

by:Barry Jones
ID: 18020788
Instead of the shaping type recordsets, I have taken the idea of your examples further and used the following code.

Thanks for your help, I have split the points between you both...

Cheers, TheFoot.



CREATE PROCEDURE CS_Catalog_All (@nCategoryID int) as      --This is a non-recursive preorder traversal.

      SET NOCOUNT ON

      DECLARE @lvl int, @line char(20), @sCategoryName nvarchar(100)

      -- Get the initial category name
      SELECT
            @sCategoryName = catdescription
      FROM
            dbo.categories
      WHERE
            categoryid = @nCategoryID

      -- Create the temp stack table
      CREATE TABLE #stack (CATEGORY_ID int, CATEGORY nvarchar(100), lvl int)
      
      -- Create the temp catalog tree table
      CREATE TABLE #catalog (CATEGORY_ID int, CATEGORY nvarchar(100), LVL int)

      -- Insert the currert category into the stack
      INSERT INTO #stack VALUES (@nCategoryID, @sCategoryName, 1)

      -- Create loop
      SELECT @lvl = 1                        
      WHILE @lvl > 0                              --From the top level going down.
      BEGIN
          IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
          BEGIN

            --Find the first node that matches current node's name.
                  SELECT
                  @nCategoryID = CATEGORY_ID      
              FROM
                  #stack
              WHERE
                  lvl = @lvl

            -- Get the category name
            SELECT
                  @sCategoryName = catdescription      
              FROM
                  dbo.categories
              WHERE
                  categoryid = @nCategoryID

            INSERT INTO #catalog (
                  CATEGORY_ID,
                  CATEGORY,
                  LVL
            ) VALUES (
                  @nCategoryID,
                  @sCategoryName,
                  @lvl - 1
            )

            --Remove the current node from the stack.
            DELETE FROM #stack
              WHERE lvl = @lvl
                AND CATEGORY_ID = @nCategoryID      

            --Insert the childnodes of the current node into the stack.
            INSERT #stack            
                    SELECT categoryid, catdescription, @lvl + 1
                    FROM categories
                    WHERE highercategoryid = @nCategoryID

            --If the previous statement added one or more nodes, go down for its first child.
              IF @@ROWCOUNT > 0            
                  --If no nodes are added, check its brother nodes.
                      SELECT @lvl = @lvl + 1      
            END
                    ELSE
                  --Back to the level immediately above.
                        SELECT @lvl = @lvl - 1            
             
      END --While

select * from #catalog order by lvl


GO
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now