SELECT DISTINCT(p.product_id), p.*, pd.*, p2s.*, pt.name as category, (p2s.price + (p.rrp * p2s.price)) as price, p2s.price as price_default, p.price as setting_price, p.manufacturer_id as mfg_id, p.default_weight as default_metal_weight FROM nj_product_to_category p2c LEFT JOIN nj_product p ON (p2c.product_id = p.product_id) LEFT JOIN nj_design_description pt ON (p.design_id = pt.design_id) LEFT JOIN nj_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN nj_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '2' AND pt.language_id = '2' AND  p.status = '1' AND p.model != ''  AND p.model != 'complimentary'  AND p.date_available <= NOW() AND p2s.store_id = '1' AND p2c.category_id = '549' AND (p2s.price_max >= 2544) AND (FIND_IN_SET(598,p.filter)  OR FIND_IN_SET(568,p.filter)  OR FIND_IN_SET(600,p.filter) ) GROUP BY p.product_id ORDER BY p.recommended ASC, LCASE(pd.product_id) DESC LIMIT 0,24