SELECT m.*, mp.id_manufacturer_page, mpl.name as manufacturer_page_name, mpl.link_rewrite as manufacturer_page_link, cl.name as category_name FROM `ps_manufacturer` m, `ps_product` p LEFT JOIN ps_manufacturer_page mp ON (p.id_manufacturer = mp.id_manufacturer AND mp.id_category = '14') LEFT JOIN ps_manufacturer_page_lang mpl ON (mp.id_manufacturer_page = mpl.id_manufacturer_page AND mpl.id_lang = '2') LEFT JOIN ps_category_lang cl ON (cl.id_lang = '2' AND cl.id_category = '14') LEFT JOIN `ps_category_product` cp ON (p.`id_product` = cp.`id_product`) WHERE 1=1 AND p.id_manufacturer = m.id_manufacturer AND cp.id_category = '14' AND p.`active` = 1 AND p.`id_product` IN ( SELECT cp.`id_product` FROM `ps_category_group` cg LEFT JOIN `ps_category_product` cp ON (cp.`id_category` = cg.`id_category`) WHERE cg.`id_group` = 1 ) GROUP BY m.id_manufacturer