Welcome to Vortx Community Forum, where you can ask questions and receive answers from the staff at Vortx and other members of the community.

If you had a user account on our previous forums website, you will need to register a new account here.

Learn more about...

AspDotNetStorefront
DotFeed

Display ALL subcategory products on a single category page

Hello Everyone,
 
My category structure is like this:(this is for a restaurant)
 
Order Online
- Location1
- - Appetizers
- - Sandwiches
- - Pizza
- - Submarines
- - - 12" Submarines
- - - 8" Submarines
 
What I would like to do is display all the products from all the subcategories that fall under Location1.  I am able to pull the products from Appetizers/Sandwiches/Pizza, but not the next level of subcategories.  The Submarines category has no products in it, since they are broken up into 12" and 8" Submarines for the POS format.
 
After some googling, I found a cached answer on the old forums, which gave me this:
 
WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
(
SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST(c.Name AS NVARCHAR(255))
FROM Category AS c
WHERE ParentCategoryID = 0
AND Deleted=0
AND Published=1
 
UNION ALL
SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(c.Name AS NVARCHAR(255))
FROM Category AS c
INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
WHERE Deleted=0
AND Published=1
 
)
 
SELECT distinct ParentCategoryID, c.CategoryID, Sort, p.ProductID, p.Name
FROM Categories c (NOLOCK)
LEFT JOIN ProductCategory pc (NOLOCK) ON pc.CategoryID = c.CategoryID
JOIN Product p (NOLOCK) ON p.ProductID = pc.ProductID
WHERE ParentCategoryID=2
 
This is what gives me all the products from the categories which are one level below Location1.
 
Can anyone steer me in the correct direction to get ALL products from ALL sub categories?
 
asked Oct 3, 2013 in General by brian (240 points)

2 Answers

0 votes

Ah I got it.  Just had to remove the WHERE ParentCategoryID=2

answered Oct 3, 2013 by brian (240 points)
0 votes
I may not be understanding what you're trying to accomplish here, but why not just map all those products to the "Location 1" category, in addition to the sub categories?
answered Oct 3, 2013 by donttryathome (165 points)
Because the point of sale system that we have integrated into the shopping cart requires the products to be separated into categories.  Of course your solution would be the best option if that wasn't the case.

As of right now, I did get the query pulling everything I need.  It's just a matter of displaying what I want.  That part's going terribly at the moment, haha.
...