Select ProductCategoryID, CategoryName,CategoryLink,CASE WHEN CHARINDEX('noimage',CategoryImage) > 0 then '' ELSE CategoryImage END as CategoryImage, CategoryColor, isNull(CategoryDescription,'') As CategoryDescription FROM HH_ProductCategory_Data,HH_ProductGroup_Data Where HH_ProductCategory_Data.ProductGroupID = HH_ProductGroup_data.ProductGroupID AND CatActive = '1' AND HH_ProductCategory_data.productCategoryID = '3';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_Product_Data.ItemOrder 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 Where HH_Product_Data.ProductCategoryID = 3 AND ProductActive = '1' AND CatActive = '1' Group By HH_Product_Data.productID, ProductName, ProductLink, ProductImageSmall, HazardShipping, CategoryLink, HH_ProductCategory_Data.ProductGroupID, dsu.DrugStoreURL , HH_Product_Data.ItemOrder Order By HH_Product_Data.ItemOrder 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 Where HH_Product_Data.ProductCategoryID = 3 AND ProductActive = '1' AND Active = '1' AND CatActive = '1' Order By ItemPrice ASC;