select prod_id,company_price, prod_name, friendly_name, prod_cat_name, products.cat_id, prod_maincat_name,brand_name, sku, image_path, prod_shipping_cost, products.date_added, price, avail_status , products.sort_order , prod_desc, upper(products.is_active) is_active, upper(products.is_featured) is_featured, pc.prod_cat_friendly_name , prod_maincat_friendly_name, product_rating, product_rating_count, products.tax_id, tax_name, coalesce(tax_percent,0) tax_percent, products.quantity_in_stock, is_offer, offer_discount_percent, offer_start_date, offer_end_date, if ( products.offer_start_date <= now() , if( products.offer_end_date >= now() , 1, 0 ) , 0 ) is_valid_offer, is_offer_active, get_prod_mrp( prod_id ) prod_mrp, get_prod_discount_amount( prod_id ) disc_amt, has_prod_discount( prod_id ) has_discount, has_product_variation_combination( products.prod_id) has_vc, get_variation_combination_with_least_price_for_product( products.prod_id) vc_with_min_price, get_price_for_prod_variation_combination(prod_id , get_variation_combination_with_least_price_for_product( products.prod_id)) price_for_min_vc, get_variation_combination_with_max_stock_for_product( products.prod_id) vc_with_max_stock, get_stock_for_prod_variation_combination(prod_id , get_variation_combination_with_max_stock_for_product( products.prod_id)) stock_for_max_vc from product_categories pc, product_maincategories pmc , products left join taxes on products.tax_id = taxes.tax_id left join product_subcategories ps on products.prod_subcat_id = ps.prod_subcat_id left join product_brands pb on products.brand_id = pb.brand_id where products.cat_id = pc.prod_cat_id and avail_status = 1 and upper(products.is_active) = 'Y' and pc.pc_maincat_id = pmc.prod_maincat_id and pc.is_active = 'Y' and pmc.prod_maincat_is_active = 'Y' and pmc.prod_maincat_friendly_name = 'patient-care' and pmc.prod_maincat_friendly_name = 'patient-care' order by sort_order