SELECT (SELECT GROUP_CONCAT(DISTINCT StandardColor,',',SKU,',',SUBSTRING(large,35),',',CONCAT(UCASE(SUBSTRING(ColorDescription, 1, 1)),LCASE(SUBSTRING(ColorDescription, 2))) SEPARATOR '|') FROM tbl_products WHERE tbl_products.RootModelNumber = p.RootModelNumber) AS AvailableColors, p.mhDescription, p.mhSlug, p.cgDescription, p.cgSlug, p.CatDescription, p.cSlug, IF(p.thumb LIKE 'http%%',p.thumb,concat( 'https://www.shopwesco.com/uploads/products/', p.thumb )) AS thumbImage, IF(p.large LIKE 'http%%',p.large, concat( 'https://www.shopwesco.com/uploads/products/', p.large )) AS largeImage, p.SKU, p.ModelStatus, p.ModelDescription, p.pkID, p.pkSKU, p.pkCategory, p.id, IF(p.CommonBrandName = 'LG Appliances','LG',p.CommonBrandName) AS RevisedBrandName, p.CommonBrandName, p.KeyFeature1, p.KeyFeature2, p.KeyFeature3, p.KeyFeature4, p.KeyFeature5, bd.CommonBrandID, IF(p.StandardColor = 'Other',CONCAT(UCASE(SUBSTRING(p.ColorDescription, 1, 1)),LCASE(SUBSTRING(p.ColorDescription, 2))),p.StandardColor) AS ModelColor, mp.MSRP, mp.Sale, mp.Available, mp.Featured, IF((mp.Sale = 0), mp.MSRP, IF( (mp.MSRP = 0), mp.Sale, IF((mp.Sale > mp.MSRP), mp.MSRP, mp.Sale))) AS Price, IF(mp.MSRP > 0 AND mp.MSRP > mp.Sale, mp.MSRP, '0.00') AS RegPrice, mp.OutletPrice, mp.quick_ship, if(bd.cart_price_message = '' OR bd.cart_price_message IS NULL,'See price in cart', cart_price_message) AS cart_message, (mp.MSRP - mp.Sale) AS Discount, IF(bd.MAP = 1 OR p.is_package = 1, 1, .90) AS MapDiscount, mp.in_stock, mp.free_shipping, p.Sale AS MinTargetPrice, p.RootModelNumber, mp.FreeShip, mp.specials, mp.closeout, bd.UMRP, (SELECT COUNT(pbm.pkID) FROM PromotionsByModel AS pbm INNER JOIN Rebates ON (Rebates.PromotionName = pbm.PromotionName AND Rebates.FileName <> '') WHERE pbm.pkProduct = p.pkID AND pbm.EffectiveDate <= CURDATE() AND pbm.ExpirationDate > CURDATE() GROUP BY pbm.pkProduct) AS rwsPromotion, p.ExpandedDescription, p.specifications, (SELECT COUNT(rbm.pkID) FROM rebatesbymodel AS rbm INNER JOIN Rebates AS r ON r.pkID = rbm.pkRebates WHERE rbm.pkProduct = p.pkID AND r.ExpirationDate > NOW() AND r.EffectiveDate < NOW() GROUP BY rbm.PromotionName) AS rwsRebates, (p.Sale * (IF(bd.MAP = 1 OR p.is_package = 1, 1,.90))) AS MAP, mh.super_category, IF(bd.no_cart =1 OR mp.on_line = 0,1,0) AS no_cart, mp.in_store, IF((mp.Sale = 0), mp.MSRP, IF ((mp.MSRP = 0 ), mp.Sale, IF((mp.Sale > mp.MSRP), mp.MSRP, mp.Sale))) AS calcPrice, c.ConsumerDescription AS cDescription FROM tbl_products AS p INNER JOIN Brand_Data AS bd ON (p.pkBrand = bd.pkID) INNER JOIN ModelPrice AS mp ON (mp.pkProduct = p.pkID AND mp.SKU = p.SKU AND mp.Available = 1) INNER JOIN menu_heading AS mh ON (mh.slug = p.mhSlug) INNER JOIN Categories AS c ON (p.cSlug = c.slug) WHERE p.mhSlug = 'dining-room-furniture' AND p.cgSlug = 'dining-cabinets' AND p.cSlug = 'hutches' AND p.SKU = '297ch6848' LIMIT 1
Forget dining out and make your home the best place to enjoy a meal with the Paradise Valley hutch. Crafted with excellence using Rubberwood solids and Split Heart White Oak veneers, this piece features a Saddle Brown finish. The hutch features beautiful under top and bead molding that transitions into chamfered pilasters. The touch LED lighting will illuminate your display and shine through the four wood framed adjustable glass shelves. The arch at the top of the case and the planked back panel will catch the eye, so the addition of wire management provisions will help to keep your display looking neat and clean. This piece cannot stand alone. Pair with the Paradise Valley buffet for a complete set. The Paradise Valley hutch captures grand opulence with comfortable rustic styling.