SELECT SQL_CALC_FOUND_ROWS ae_prod_products_in_store.storeid, ae_prod_products_in_store.productid, ae_prod_products_info.*, (SELECT count(*) FROM `ae_prod_product_reviews` WHERE ae_prod_product_reviews.productid = ae_prod_products_info.id AND `ae_prod_product_reviews`.`rating` = 'P' AND `ae_prod_product_reviews`.`status` = 'A') as positivereviewcount, (SELECT count(*) FROM `ae_prod_product_reviews` WHERE ae_prod_product_reviews.productid = ae_prod_products_info.id AND `ae_prod_product_reviews`.`rating` = 'N' AND `ae_prod_product_reviews`.`status` = 'A') as neutralreviewcount, (SELECT count(*) FROM `ae_prod_product_reviews` WHERE ae_prod_product_reviews.productid = ae_prod_products_info.id AND `ae_prod_product_reviews`.`rating` = 'M' AND `ae_prod_product_reviews`.`status` = 'A') as negativereviewcount, ae_prod_categories.name as categoryname, ae_prod_categories.url as categorynameurl, ae_prod_products_images.thumbimage, ae_prod_products_price_info.mrp, ae_prod_products_price_info.offer_price, ae_prod_products_price_info.offer_percentage, ae_prod_products_favorites.userid FROM `ae_prod_products_in_store` LEFT JOIN `ae_prod_products_info` ON `ae_prod_products_info`.`id` = `ae_prod_products_in_store`.`productid` LEFT JOIN `ae_prod_categories` ON `ae_prod_products_info`.`cat_id` = `ae_prod_categories`.`id` LEFT JOIN `ae_prod_products_images` ON `ae_prod_products_info`.`id` = `ae_prod_products_images`.`product_id` AND `ae_prod_products_images`.`is_primary` = "1" LEFT JOIN `ae_prod_products_price_info` ON `ae_prod_products_price_info`.`product_id` = `ae_prod_products_info`.`id` LEFT JOIN `ae_prod_products_favorites` ON `ae_prod_products_favorites`.`productid` = `ae_prod_products_info`.`id` WHERE `ae_prod_products_in_store`.`storeid` = '174' AND `ae_prod_products_info`.`IsPublished` = '1' AND ae_prod_products_price_info.mrp = (SELECT ae_prod_products_price_info.mrp FROM `ae_prod_products_price_info` WHERE ae_prod_products_price_info.product_id = ae_prod_products_info.id ORDER BY `ae_prod_products_price_info`.`mrp` ASC LIMIT 1) LIMIT 20