SELECT DISTINCT m.manufacturers_id as id,
               m.manufacturers_name as name 
                                  FROM products p
                                  JOIN products_description pd
                                       ON p.products_id = pd.products_id
                                          AND pd.language_id = '1'
                                          AND trim(pd.products_name) != ''
                                  JOIN manufacturers m 
                                       ON m.manufacturers_id = p.manufacturers_id
                                          AND m.manufacturers_status = 1
                                        JOIN products_to_categories p2c 
                     ON p2c.products_id = p.products_id
                        AND p2c.categories_id = '120501' 
                                 WHERE p.products_status = '1'
                                       
                                        AND (p.supporter_club = '')  AND p2c.super_parent_id = '120348'  AND p.products_master = 1 
                              ORDER BY name