Majestic

select p.theme_only, p.products_exturl, p.products_group, p.products_image, pd.products_name, p.products_model, m.manufacturers_name, p.products_id, p.manufacturers_id, p.products_price, 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 from products p left join products_to_categories ptc ON(p.products_id=ptc.products_id) left join products_description pd ON(p.products_id=pd.products_id) left join manufacturers m ON(p.manufacturers_id = m.manufacturers_id) left join specials s on p.products_id = s.products_id left join featured f on p.products_id = f.products_id WHERE p.products_status = '1' AND m.manufacturers_id IN(271) AND p.products_group IN('m','u') AND p.theme_only = 0 AND p.products_type NOT IN (8,88,888) and pd.language_id = '2' group by p.products_id order by p.products_lug DESC, pd.products_name