June 15, 2009 at 11:24 am
Hello,
I am trying to build a Javascript "category menu" on an ecommerce site. Therefore, I am trying retrieve all the categories and parent categories from my database in depth order, if that makes any sense. The DB model is like this:
CategoryID, Category Description, ParentCategoryID
So basically I need a SQL query that would give me back the results like this:
CategoryID, Descript, ParentCategoryID, Depth
1 - Parent Cat - NULL - 0
2 - Sub Cat - 1 - 1
3 - Sub Cat2 - 1 1
4 - Sub Sub Cat - 3 - 2
5 - Parent Cat2 - NULL - 0
6 - Sub Cat - 5 - 1
so that I build the categories/levels for the javascript menu. I did something like this like 7 or 8 years ago (there was a built-in article in SQL 2000 Transact Help) with one query if I remember correctly, but I could be incorrect. I don't want to create a temp table.
How would I go about doing this? Many thanks in advance.
June 15, 2009 at 12:09 pm
Have a look to BOL (books online) for "common table expressions" there you will find an example for a recursive CTE which should exactly fit to your problem.
June 15, 2009 at 1:55 pm
Thanks for the reply; I looked at CTE help in SQL Books, but that didn't help because the method they mention is having only one 'anchor' member (one base member that everything falls under). This isn't the case with the way the Ecommerce Categories are set up; the root categories all have ParentCat = null.
I reworked that example and came up with this:
==============
WITH CategoryTree (Category, Descript, ParentCat, Level)
AS
(
-- Anchor member definition
SELECT Category, Descript, ParentCat, 0 AS Level
FROM Category (NOLOCK)
WHERE ParentCat IS NULL
UNION ALL
-- Recursive member definition
SELECT c.Category, c.Descript, c.ParentCat, Level + 1
FROM Category c
INNER JOIN CategoryTree ct
ON c.ParentCat = ct.Category
)
================
The problem is that this returns all the parent categories first (level 0), then after that it shows all the sub categories of the very last parent category it retrieved, and works it way backwards like that.
So, basically:
ParentCat1 - 0
ParentCat2 - 0
ParentCat3 - 0
ParentCat4 - 0
ParentCat5 - 0
SubCatofParentCat5 - 1
SubCatofParentCat5 - 1
SubCatofParentCat4 - 1
SubCatofParentCat3 - 1
etc etc.
I need it to look like this:
ParentCat1 - 0
SubCatofParentCat1 - 1
SubCatofParentCat1 - 1
ParentCat2 - 0
SubCatofParentCat2 - 1
ParentCat3 - 0
ParentCat4 - 0
SubCatofParentCat4 - 1
ParentCat5 - 0
SubCatofParentCat5 - 1
SubCatofParentCat5 - 1
Any suggestions?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply