Help with generating a report from data in a parent-children model


I need help with a problem regarding data saved in a parent-children model table and a report I need to build upon it. I've already tried searching for topics about parent-children issues, but I couldn't find anything useful in my scenario.

<strong>What I have</strong>

A Microsoft SQL Server 2000 database server.

A categories table, which has four columns: category_id, category_name, father_id and visible; the categories have <em>x</em> root categories (where <em>x</em> is variable), and could be <em>y</em> level deep (where <em>y</em> is variable), if a category is a root level one it has father_id null otherwise it's filled with the id of the father category.

A sales table, which has <em>z</em> columns, one of which is category_id, a foreign key to categories.category_id; a sale must always have a category, and it could be linked anywhere in the aforementioned <em>y</em> level.

<strong>What I need</strong>

I've been asked a report displaying only the root (first level) categories, and the quantity of sales belongings to each of these, or their children, no matter how deep. I.e. if one of the root categories is food, which has a children category named fruit, which has a children category named apple, I need to count every item belonging to food or fruit or apple.

<strong>Couldn't you use the nested set data model?</strong>

I know of the nested set model, but I already have the table this way, and migrating it to the nested set model would be a pain (let alone I didn't even fully grasp how nested set works), not counting the changes needed in the application using the database. (If someone thinks this is still the least pain way, please explain why and how the current data could be migrated.)

<strong>Couldn't you use CTE (Common Table Expressions)?</strong>

No, it's a Microsoft SQL Server <em>2000</em>, and Common Table Expressions are introduced in the <em>2005</em> edition.

Thanks in advance, Andrea.


<h2>SQL 2000 Based solution</h2> DECLARE @Stack TABLE ( StackID INTEGER IDENTITY , Category VARCHAR(20) , RootID INTEGER , ChildID INTEGER , Visited BIT) INSERT INTO @Stack SELECT [Category] = c.category_name , [RootID] = c.category_id , [ChildID] = c.category_id , 0 FROM Categories c WHILE EXISTS (SELECT * FROM @Stack WHERE Visited = 0) BEGIN DECLARE @StackID INTEGER SELECT @StackID = MAX(StackID) FROM @Stack INSERT INTO @Stack SELECT st.Category , st.RootID , c.category_id , 0 FROM @Stack st INNER JOIN Categories c ON c.father_id = st.ChildID WHERE Visited = 0 UPDATE @Stack SET Visited = 1 WHERE StackID <= @StackID END SELECT st.RootID , st.Category , COUNT(s.sales_id) FROM @Stack st INNER JOIN Sales s ON s.category_id = st.ChildID GROUP BY st.RootID, st.Category ORDER BY st.RootID <h2>SQL 2005 Based solution</h2>

A <a href="http://msdn.microsoft.com/en-us/library/ms190766.aspx" rel="nofollow">CTE</a> should get you what you want

<ul><li>Select each category from Categories to be the root item</li> <li>recursively add each child of every root item</li> <li>INNER JOIN the results with your sales table. As every <em>root</em> is in the result of the CTE, a simple GROUP BY is sufficient to get a count for each item.</li> </ul>

<strong>SQL Statement</strong>

;WITH QtyCTE AS ( SELECT [Category] = c.category_name , [RootID] = c.category_id , [ChildID] = c.category_id FROM Categories c UNION ALL SELECT cte.Category , cte.RootID , c.category_id FROM QtyCTE cte INNER JOIN Categories c ON c.father_id = cte.ChildID ) SELECT cte.RootID , cte.Category , COUNT(s.sales_id) FROM QtyCTE cte INNER JOIN Sales s ON s.category_id = cte.ChildID GROUP BY cte.RootID, cte.Category ORDER BY cte.RootID


Something like this?

CREATE TABLE #SingleLevelCategoryCounts { category_id, count, root_id } CREATE TABLE #ProcessedCategories { category_id, root_id } CREATE TABLE #TotalTopLevelCategoryCounts { category_id, count } INSERT INTO #SingleLevelCategoryCounts SELECT category_id, SUM(*), category_id FROM Categories INNER JOIN Sales ON Categories.category_id = sales.category_id WHERE Categories.father_id IS NULL GROUP BY Categories.category_id WHILE EXISTS (SELECT * FROM #SingleLevelCategoryCounts) BEGIN IF NOT EXISTS(SELECT * FROM #TopLevelCategoryCounts) BEGIN INSERT INTO #TopLevelCategoryCounts SELECT root_id, count FROM #SingleLevelCategoryCounts END ELSE BEGIN UPDATE top SET top.count = top.count + level.count FROM #TopLevelCategoryCounts top INNER JOIN #SingleLevelCategoryCounts level ON top.category_id = level.count END INSERT INTO #ProcessedCategories SELECT category_id, root_id FROM #SingleLevelCategoryCounts DELETE #SingleLevelCategoryCounts INSERT INTO #SingleLevelCategoryCounts SELECT category_id, SUM(*), pc.root_id FROM Categories INNER JOIN Sales ON Categories.category_id = sales.category_id INNER JOIN #ProcessedCategories pc ON Categories.father_id = pc.category_id WHERE Categories.category_id NOT IN ( SELECT category_id in #ProcessedCategories ) GROUP BY Categories.category_id END


  • Parse.com include related objects in one to many relationship
  • Injecting JSP from Spring MVC controller
  • read single line from text file in objective-C
  • iOS credit card processing [closed]
  • React Native - manipulate View to apply shadow to Image's BorderRadius?
  • C++: Use input string as variable name
  • Find duplicate values in one of the two columns in a text file
  • git post-receive hook to update multiple servers
  • Trying to total columns for relation datasource
  • knockout error - Uncaught Error: Unable to parse bindings.
  • Why is it still possible to insert a foreign key that doesn't exist?
  • LINQ join with filter criteria
  • CloseOptionsMenu doesn't work?
  • Java : How to tint this PNG programmatically?
  • how to populate a SQLite database and use that database in phonegap?
  • sweetalert2 inputoptions from file in select example
  • HttpListener.IsSupported is false on XP SP3
  • jQuery: How to AJAXify WordPress Search?
  • CERN ROOT exporting data to plain text
  • jwtBearer bearer token with rc-1 update to ASP.Net 5
  • UIAlertController button function not working
  • MYSQ & MVC3 SQL connection error \\ ProviderManifestToken but I am using MySQL
  • Array with custom indexes in Ionic2
  • Django simple Captcha “No module named fields” error
  • jQuery ready not fired after rails link_to is clicked
  • PHP CURL timing out but CLI CURL works
  • Do I need to reset a Perl hash index?
  • Textfile Structure (tables)
  • Why querying a date BC is changed to AD in Java?
  • Why does access(2) check for real and not effective UID?
  • Django: Count of Group Elements
  • ilmerge with a PFX file
  • Why value captured by reference in lambda is broken? [duplicate]
  • How would I use PHP exceptions to define a redirect?
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • Why joiner is not used after Sequence generator or Update statergy
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Recursive/Hierarchical Query Using Postgres
  • UserPrincipal.Current returns apppool on IIS