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 = 'living-room-furniture' AND p.cgSlug = 'living-room-groups' AND p.cSlug = 'living-room-groupsets' AND p.SKU = '297ot3pcs' LIMIT 1
Create an unforgettable setting of casual elegance with the Paradise Valley three piece set. Crafted with Rubberwood solids and Split Heart White Oak veneers, this set features a Saddle Brown finish and includes the rectangular cocktail table and two rectangular end tables. The cocktail table has a beautiful blue stone insert on the top that radiates a natural earthy feel. Casters ensure easy mobility. The end tables have a full extension drawer with a wood framed drawer front that is accented with Antique Pewter knob hardware. The cocktail and end tables have chamfered legs that flow to an open bottom shelf for additional storage or display items, and square tapered feet. The Paradise Valley three piece set captures grand opulence with comfortable rustic styling.