SELECT p.SKU, p.mhSlug, p.mhDescription, p.cgSlug, p.cSlug, p.CatDescription, p.CommonBrandName, bd.CommonBrandID, IF(p.StandardColor = 'Other',CONCAT(UCASE(SUBSTRING(p.ColorDescription, 1, 1)),LCASE(SUBSTRING(p.ColorDescription, 2))),p.StandardColor) AS ModelColor, p.StandardColor, p.ColorDescription, 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 calcPrice, IF(mp.MSRP > 0 AND mp.MSRP > mp.Sale, mp.MSRP, '0.00') AS RegPrice, mp.OutletPrice, mp.quick_ship, mp.PowerBuy, (mp.MSRP - mp.Sale) AS Discount, mp.in_stock, mp.free_shipping, p.Sale AS MinTargetPrice, mp.FreeShip, bd.UMRP, (p.Sale * (IF(bd.MAP = 1 OR p.is_package = 1, 1,.90))) AS MAP, (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, (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 > CURDATE() AND r.EffectiveDate < CURDATE() GROUP BY rbm.PromotionName) AS rwsRebates FROM Categories AS c INNER JOIN Category_Groups AS cg ON (c.Category_Group = cg.CategoryCode) INNER JOIN menu_heading AS mh ON (cg.MenuHeading = mh.id) INNER JOIN tbl_products AS p ON (p.cSlug = c.slug AND p.cgSlug = cg.slug AND p.mhSlug = mh.slug) INNER JOIN Brand_Data AS bd ON (bd.pkID = p.pkBrand) INNER JOIN tbl_store_brands AS sb ON (sb.brandID = bd.pkID AND sb.categoryID = c.pkID) INNER JOIN ModelPrice AS mp ON (mp.pkProduct = p.pkID AND mp.SKU = p.SKU AND mp.Available = 1 ) WHERE ((`p`.`mhSlug` = 'cooking') and (`p`.`cgSlug` = 'ranges')) GROUP BY p.pkID ORDER BY in_stock DESC, if(mp.Featured = 1 , display_sequence, 9999) ASC, PowerBuy DESC, preferred DESC, p.CommonBrandName, p.SKU
Welcome to our website! As we have the ability to list over one million items on our website (our selection changes all of the time), it is not feasible for a company our size to record and
playback the descriptions on every item on our website. However, if you are an American with a disability we are here to help you. Please call our disability services phone line at
719.596.3000 during regular business hours and one of our kind and friendly personal shoppers will help you navigate through our website, help conduct advanced searches, help you choose the item
you are looking for with the specifications you are seeking, read you the specifications of any item and consult with you about the products themselves.
place an order if you so desire.