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.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 from product_categories pc, product_maincategories pmc , products 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 = 'soup-cup' or pc.prod_cat_name = 'soup-cup' ) AND ( pc.prod_cat_friendly_name = 'soup-cup' or pc.prod_cat_name = 'soup-cup' ) and pmc.prod_maincat_friendly_name = 'crockery' order by sort_order
NO FILTERS
Price

Apply
Brands

Wilmax England

BRASS LINE

AART

DANKOTUWA

NORITAKE

SILVETTA

IVEO

NACHTMANN

Apply