• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Getting recursive values of sub records in MSSQL 2005

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
ccleebelt
Asked:
ccleebelt
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the hierarchical part is easy, using the CTE method in SQL 2005...
did you achieve that already?
0
 
ccleebeltAuthor Commented:
no - I am just getting this started.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, lets' see to get part 1 done, the recursion without the counts yet
http://msdn2.microsoft.com/en-us/library/ms186243.aspx
0
 
ccleebeltAuthor Commented:
actually, this does it...thanks.  I can just add the count(*) to the last query and go from there.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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