Dear Dynamicweb,
Can you please add to only look at active products when calculating the group hierarchy?
This is the query today:
WITH Hierarchy AS
(
SELECT DISTINCT GroupProductRelationGroupId AS GroupID
FROM EcomGroupProductRelation INNER JOIN
(
SELECT ProductId
FROM EcomProducts INNER JOIN
(
SELECT AssortmentItemProductID, AssortmentItemLanguageID
FROM EcomAssortmentItems INNER JOIN EcomAssortments ON AssortmentItemAssortmentID = AssortmentID AND AssortmentItemLanguageID = AssortmentLanguageID
WHERE AssortmentActive = 1 AND AssortmentID = {0} AND AssortmentLanguageID = {1}
) t1 ON ProductId = AssortmentItemProductID AND ProductLanguageId = AssortmentItemLanguageID
) t2 ON GroupProductRelationProductId = ProductID
UNION ALL
SELECT GroupRelationsParentID
FROM EcomGroupRelations
INNER JOIN Hierarchy ON GroupRelationsGroupID = GroupID
)
SELECT * FROM Hierarchy
Best regards,
Anders
