Solved

Getting recursive values of sub records in MSSQL 2005

Posted on 2007-11-14
4
218 Views
Last Modified: 2011-09-20
I have a table called "categories" which contains all categories of "widgets".  Categories can be "sub_of" as well.  There is a field.  The structure looks like.

category_id int id
category_name varchar (50)
sub_of int

The data look like this

category_id             category_name                        sub_of
--------------------------------------------------------------------------
1                             Widgets                                        0   -  this is the root.
2                             Nice Widgets                                 1
3                             Cool Widgets                                1
4                             Very Cool widgets                         3

In my "widgets" table there is reference (fk) to category_id to categorize the widgets.

Question - I need to create a query to quickly return the contents and counts in each category.

For example, if I had 10 widgets total, the counts would look like:

>Widgets (10)
    >Nice Widgets (5)
    >Cool Widgets (5)   (note: 3 in this category and two below).
        > Very Cool Widgets (2)

So if I ask for all Widgets in "widgets" I get all widgets not only spefically in that category, but in all sub categories as well.  I also need the counts in similar manner.

I need actual code examples please.

Thank you.
0
Comment
Question by:ccleebelt
  • 2
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20282542
the hierarchical part is easy, using the CTE method in SQL 2005...
did you achieve that already?
0
 

Author Comment

by:ccleebelt
ID: 20282684
no - I am just getting this started.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20283312
ok, lets' see to get part 1 done, the recursion without the counts yet
http://msdn2.microsoft.com/en-us/library/ms186243.aspx
0
 

Author Comment

by:ccleebelt
ID: 20283498
actually, this does it...thanks.  I can just add the count(*) to the last query and go from there.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

929 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

14 Experts available now in Live!

Get 1:1 Help Now