Select DISTINCT HH_ShopByCategory_Data.ShopByCategoryID as ProductCategoryID, ShopByName as CategoryName, CategoryLink,CASE WHEN CHARINDEX('noimage',ShopByImage) > 0 then '' ELSE ShopByImage END as CategoryImage, ShopByName as CategoryColor, '' As CategoryDescription, CASE WHEN HH_ProductCategory_Data.ProductGroupID = 2 Then 1 Else 0 End as isEyeCare FROM HH_ProductGroup_Data, HH_ProductCategory_Data INNER JOIN HH_Product_Data ON HH_Product_Data.ProductCategoryID = HH_ProductCategory_Data.ProductCategoryID INNER JOIN HH_ProductItem_Data ON HH_ProductItem_Data.ProductID = HH_Product_Data.ProductID INNER JOIN HH_ShopByLookUp_Data ON HH_ShopByLookUp_Data.thisIDvalue = HH_Product_Data.ProductID AND HH_ShopByLookup_Data.thisIDname = 'ProductID' INNER JOIN HH_ShopByCategory_data ON HH_ShopByCategory_Data.ShopByCategoryID = HH_ShopByLookUp_Data.ShopByID Where HH_ProductCategory_Data.ProductGroupID = HH_ProductGroup_data.ProductGroupID AND CatActive = '1' AND ShopByActive = '1' AND HH_Product_Data.ProductActive = '1' AND HH_ProductItem_Data.Active = '1' AND HH_ShopByCategory_Data.ShopByCategoryID = '2';Select DISTINCT HH_Product_Data.ProductID, ProductName, CASE WHEN HH_ProductCategory_Data.ProductGroupID = 2 Then '/Ingredient/Cornflower/'+ProductLink+'.htm' Else '/HairCare/' + CategoryLink+'/' End + ProductLink+'.htm' As ProductLink, ProductimageSmall as ProductImage, HazardShipping, dsu.DrugStoreURL, isnull(COUNT(ProductReviewID),0) As ReviewCount, isnull(AVG(Rating),0) As Rating , HH_ShopByLookUp_Data.ShopByOrder FROM HH_Product_Data LEFT JOIN HH_ProductReview_Data ON HH_ProductReview_Data.ProductID = HH_Product_Data.ProductID AND HH_ProductReview_Data.Active = '1' AND HH_ProductReview_Data.ReviewStatus = 'A' INNER JOIN HH_ProductCategory_Data ON HH_ProductCategory_Data.ProductCategoryID = HH_Product_Data.ProductCategoryID INNER JOIN HH_ProductItem_Data ON HH_Product_Data.productID = HH_ProductItem_data.ProductID INNER JOIN ( SELECT A.ProductID, A.DrugStoreURL FROM HH_ProductItem_Data as A WHERE dbo.UDF_parsefloat(A.ItemSize) >= (SELECT MAX(dbo.UDF_parsefloat(B.ItemSize)) FROM HH_ProductItem_Data B WHERE B.ProductID=A.ProductID)) dsu ON HH_Product_Data.ProductID = dsu.ProductID INNER JOIN HH_ShopByLookUp_Data ON HH_ShopByLookUp_Data.thisIDvalue = HH_Product_Data.ProductID AND HH_ShopByLookup_Data.thisIDname = 'ProductID' INNER JOIN HH_ShopByCategory_data ON HH_ShopByCategory_Data.ShopByCategoryID = HH_ShopByLookUp_Data.ShopByID Where HH_ShopByCategory_Data.ShopByCategoryID = '2' AND ProductActive = '1' AND CatActive = '1' Group By HH_Product_Data.productID, ProductName, ProductLink, ProductImageSmall, HazardShipping, CategoryLink, HH_ProductCategory_Data.ProductGroupID, dsu.DrugStoreURL , HH_ShopByLookUp_Data.ShopByOrder Order By HH_ShopByLookUp_Data.ShopByOrder ASC ; Select HH_Product_Data.ProductID, ProductName, ProductItemID, ItemSize As ItemText, ItemPrice, HH_ProductItem_Data.SAPCode, HH_ProductItem_Data.EANCode, HH_ProductItem_Data.DrugStoreURL, CASE WHEN isNull(HH_ItemInventory_Data.QtyAvailable,0) >= 30 Then 1 Else 0 End As IsAvailable FROM HH_Product_Data INNER JOIN HH_ProductItem_Data ON HH_Product_Data.ProductID = HH_ProductItem_Data.ProductID LEFT JOIN HH_ItemInventory_Data ON HH_ProductItem_Data.SAPCode = HH_ItemInventory_Data.ItemCode INNER JOIN HH_ProductCategory_Data ON HH_ProductCategory_Data.ProductCategoryID = HH_Product_Data.productCategoryID INNER JOIN HH_ShopByLookUp_Data ON HH_ShopByLookUp_Data.thisIDvalue = HH_Product_Data.ProductID AND HH_ShopByLookup_Data.thisIDname = 'ProductID' INNER JOIN HH_ShopByCategory_data ON HH_ShopByCategory_Data.ShopByCategoryID = HH_ShopByLookUp_Data.ShopByID Where HH_ShopByCategory_Data.ShopByCategoryID = '2' AND ProductActive = '1' AND Active = '1' AND CatActive = '1' Order By ItemPrice ASC;