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') and (`p`.`cSlug` = 'electric-freestanding-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