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 ( pc.prod_cat_friendly_name = 'Glassy'
or pc.prod_cat_name = 'Glassy' ) AND ( pc.prod_cat_friendly_name = 'Glassy'
or pc.prod_cat_name = 'Glassy' ) and pmc.prod_maincat_friendly_name = 'colour' AND has_prod_item_property_mapping_by_name(prod_id, '', 'taupe') > 0 order by sort_order