Select HH_ProductCategory_Data.ProductCategoryID, HH_Product_Data.ProductID as ProductID,HH_Product_Data.HazardShipping, ProductName,ProductLink, CASE WHEN NOT ProductImageMedium = '' Then ProductImageMedium WHEN CHARINDEX('noimage',ProductImage) > 0 then '' ELSE ProductImage END as ProductImage, isNull(Description,'') As Description, Slogan,ProductItemID,ItemSize,dbo.GetCurrency(ItemPrice) as ItemPrice,CategoryName,CategoryLink,isNull(CategoryDescription,'') As CategoryDescription,CategoryColor,PlantBenefits , isnulL(Ingredients,'') As Ingredients, ProductImage as LargeImage, isnull(AboutPlant,'') As AboutPlant, Directions, CASE WHEN isNull(i.QtyAvailable,0) >= 30 Then 1 Else 0 End As IsAvailable, CASE WHEN HH_ProductCategory_Data.ProductGroupID = 2 Then 1 Else 0 End as isEyeCare, HH_ProductItem_Data.DrugStoreURL, isnull(VideoPath,'') As VideoPath, isnull(ScreenShot,'') As VideoImage, OriginImage as Thumbnail From HH_ProductCategory_Data LEFT JOIN HH_Video_Data ON HH_ProductCategory_Data.VideoID = HH_Video_Data.VideoID INNER JOIN HH_Product_Data ON HH_ProductCategory_Data.ProductCategoryID = HH_Product_Data.ProductCategoryID INNER JOIN HH_ProductItem_Data ON HH_Product_Data.ProductID = HH_ProductItem_Data.ProductID LEFT JOIN HH_ItemInventory_Data i ON HH_ProductItem_Data.SAPCode = i.ItemCode Where CatActive = '1' AND ProductActive='1' AND HH_Product_Data.ProductID = '344' Order By HH_Product_Data.ItemOrder ASC;; Select Top 5 DateAdded,ReviewName,Rating, Review, (Select AVG(Rating) From HH_ProductReview_Data Where ReviewStatus = 'A' AND Active = '1' AND ProductID = '344') as AvgRating From HH_ProductReview_Data Where ReviewStatus = 'A' AND Active = '1' AND ProductID = '344' order By ProductReviewID DESC ; Select Distinct p.ProductID, p.HazardShipping, c.CategoryLink, p.productName,p.productLink, ProductImageSmall, CASE WHEN CHARINDEX('noimage',p.ProductImageRelated) > 0 or p.ProductImageRelated = '' then p.ProductImageSmall ELSE p.ProductImageRelated END as ProductImage, CASE WHEN isNull(i.QtyAvailable,0) >= 30 Then 1 Else 0 End As IsAvailable, HH_ProductItem_Data.DrugStoreURL, ProductItemID,HH_ProductItem_Data.SAPCode as Code,ItemSize,dbo.GetCurrency(ItemPrice) as ItemPrice,HH_ProductRelated_Data.ItemOrder as RelatedOrder From HH_Product_Data p INNER JOIN HH_ProductItem_Data ON p.ProductID = HH_ProductItem_Data.ProductID INNER JOIN HH_ProductCategory_Data c ON c.ProductCategoryID = p.ProductCategoryID LEFT JOIN HH_ItemInventory_Data i ON HH_ProductItem_Data.SAPCode = i.ItemCode INNER JOIN HH_ProductRelated_Data ON HH_ProductItem_Data.SAPCode IN ( SElect Distinct ProductCode From HH_ProductRelated_Data,HH_ProductItem_data where HH_ProductRelated_Data.RelatedCode = HH_ProductItem_Data.SAPCode AND HH_ProductItem_Data.ProductID ='344' ) Where c.CatActive = '1' AND p.ProductActive='1'
Klorane | Hair & Eye Care Products | Plant-Based Hair Care
>