Thread: aspdnsf_EntityMgr Modification

I am trying to add a field to the following query that outputs the most looked at product image so I can use it as my category picture. Below is my current modified query but if there aren't any products tied to the categoryID, it doesn't go to the sub category to look for the sku/imagefilenameoverride. The part highlighted in red is my change. Any suggestions to make it pull farther down or combine all sub category products and pull the most looked at one?

Code:

    IF @EntityName = 'Category' BEGIN
        SELECT Entity.CategoryID EntityID, Entity.CategoryGUID EntityGuid, Name,ColWidth,Description,SEName,SEKeywords,SEDescription,SETitle,SENoScript,SEAltText,ParentCategoryID ParentEntityID,DisplayOrder,SortByLooks,XmlPackage,Published,ContentsBGColor,PageBGColor,GraphicsColor
			   ,isnull(NumProducts, 0) NumObjects, PageSize, QuantityDiscountID, Summary, SkinID, TemplateName 
			   , (SELECT TOP 1 
					CASE WHEN isnull(CAST(Product.ImageFilenameOverride as varchar),'') = '' THEN Product.SKU
						ELSE Product.ImageFilenameOverride END AS EntityImage
					FROM Product 
					INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID 
					INNER JOIN Category ON ProductCategory.CategoryID = Category.CategoryID
				  WHERE Category.CategoryID = Entity.CategoryID
				  ORDER BY Product.Looks DESC) EntityImage
        FROM dbo.Category Entity with (NOLOCK)
          left join (SELECT pc.CategoryID, COUNT(pc.ProductID) AS NumProducts
                     FROM  dbo.ProductCategory pc with (nolock)
                         join [dbo].Product p with (nolock) on pc.ProductID = p.ProductID  and p.deleted=0 and p.published=1
                     GROUP BY pc.CategoryID
                    ) a on Entity.CategoryID = a.CategoryID
        WHERE Published >= @PublishedOnly and Deleted=0
        ORDER BY ParentCategoryID,DisplayOrder,Name
    END

It looks like making the following modification always outputs a value, but is this the best way to do it?

Code:

WHERE Category.CategoryID = Entity.CategoryID OR Category.ParentCategoryID = Entity.CategoryID