Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 545
  • Last Modified:

Hierarchical Recordset HELP!

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
Barry Jones
Asked:
Barry Jones
  • 2
  • 2
2 Solutions
 
imran_fastCommented:
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
 
AtulKamdarCommented:
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
 
imran_fastCommented:
hi TheFoot ,
What version of sql you have 2000 or 2005?
0
 
Barry JonesAuthor Commented:
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
 
Barry JonesAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now