select SQL_CALC_FOUND_ROWS distinct
p.products_is_bundle, p.products_id, p.products_sale_type_cd, p.products_part_number, p.products_quantity,
p.products_eta, p.byorder, p.products_price, p.products_image, p.products_price_rrp, p.products_tax_class_id,
if(s.status, s.specials_new_products_price, null) as specials_new_products_price,
if(s.status, s.specials_new_products_price, p.products_price) as final_price,
p.products_fixed_price, p.products_fixed_price_from_date, p.products_fixed_price_to_date,
p.products_availability_list, pd.products_name, pd.products_description, pd.products_description_short,
pd.products_seo_text, pd.products_viewed, m.manufacturers_name, sk.seo_keywords_short_text
from products p
inner join products_description pd on p.products_id = pd.products_id
inner join manufacturers m on p.manufacturers_id = m.manufacturers_id
inner join products_to_categories p2c on p.products_id = p2c.products_id
inner join seo_keywords sk on pd.seo_keywords_id = sk.seo_keywords_id
left join specials s on p.products_id = s.products_id left join tax_rates tr on p.products_tax_class_id = tr.tax_class_id left join zones_to_geo_zones gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '13') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '185') where p.products_status = 1 and pd.language_id = 1 and p2c.categories_id = 1254 and m.manufacturers_id = 200 order by final_price asc limit 0, 40