As promised, here is the follow-up to the last post. This query gets us the names of all subcategories of a particular category in Commerce Server 2007.

DECLARE @categoryOid AS INT
SET @categoryOid = 937 ;

WITH    Cats2 ( oid, child_oid, hierarchy_level )
          AS ( SELECT   oid ,
                        child_oid ,
                        1 AS hierarchy_level
               FROM     dbo.ConsumerShop_CatalogHierarchy
               WHERE    oid = @categoryOid
                        AND CatalogName = 'ConsumerStaging'
               UNION ALL
               SELECT   h.oid ,
                        h.child_oid ,
                        c.hierarchy_level + 1 AS hierarchy_level
               FROM     dbo.ConsumerShop_CatalogHierarchy h
                        JOIN Cats2 c ON h.oid = c.child_oid
                                        AND h.CatalogName = 'ConsumerStaging'
             )
    SELECT  CategoryName
    FROM    dbo.ConsumerShop_CatalogProducts
    WHERE   oid IN ( SELECT DISTINCT ( oid ) 
					 FROM   Cats2 )

Commerce Server API is not exactly known for being lightning-fast, so sometimes you want to skip it altogether and just use plain old SQL to get the data.

This time I needed to find all categories (at a particular level) that a product belongs to. This was solved by the script below, which is using a recursive CTE to walk upwards through the tree.

Just set the @productId and @hierarchyLevel below and voilá!

DECLARE @hierarchyLevel INT
DECLARE @productId VARCHAR(10)
SET @hierarchyLevel = 3
SET @productId = '25853' ;

WITH Cats (oid, child_oid, hierarchy_level) AS
	(
		SELECT h.oid, h.child_oid, 1 AS hierarchy_level
		FROM dbo.ConsumerShop_CatalogHierarchy h 
		JOIN dbo.ConsumerShop_CatalogProducts p ON h.child_oid = p.oid
		WHERE p.ProductID = @productId
		AND h.CatalogName = 'ConsumerStaging'

		UNION ALL 

		SELECT h.oid, h.child_oid, c.hierarchy_level + 1 AS hierarchy_level
		FROM dbo.ConsumerShop_CatalogHierarchy h
		JOIN Cats c ON c.oid = h.child_oid
		WHERE h.CatalogName = 'ConsumerStaging'
	)
    SELECT  CategoryName
    FROM    Cats
            JOIN dbo.ConsumerShop_CatalogProducts p ON Cats.oid = p.oid
    WHERE   hierarchy_level = @hierarchyLevel
            AND BaseCatalogName = 'ConsumerStaging'

In my next post I will explain how to accomplish the opposite, i.e. find all subcategories of a category.