SELECT c.*,
                cl.id_lang,
                cl.name,
                cl.description,
                cl.link_rewrite,
                cl.meta_title,
                cl.meta_keywords,
                cl.meta_description
            FROM `ps_category` c
            LEFT JOIN `ps_category_lang` cl ON (c.`id_category` = cl.`id_category` AND `id_lang` = 6)
            LEFT JOIN `ps_category_group` cg ON (cg.`id_category` = c.`id_category`)
            WHERE (`id_parent` = 296)
                AND (`active` = 1)
                AND (cg.`id_group` = 1)
            GROUP BY c.`id_category`
            ORDER BY `cl`.`name` ASC