Solved

Hierarchical Recordset HELP!

Posted on 2006-11-24
5
470 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 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

23 Experts available now in Live!

Get 1:1 Help Now