SELECT p.m_id, p.m_ref, p.m_shortdescription, p.m_finish, p.m_new, p.m_specialorder, i.m_image, s.m_name, s.m_id as s_id, (SELECT i2.m_image FROM s_products p2 INNER JOIN product_images i2 ON i2.m_productid = p2.m_id WHERE p2.m_ref = i2.m_ref ORDER BY CASE p2.m_primary WHEN 'Y' THEN '1' ELSE '999' END asc LIMIT 1) as productimage FROM s_products p INNER JOIN s_subcategories s ON s.m_id = p.m_subcategory INNER JOIN product_images i ON i.m_productid = p.m_id WHERE p.m_active = 'Y' AND p.m_specification = '' AND EXISTS ( SELECT 1 FROM s_filter_producttags WHERE product_id = p.m_id AND filtertag_id IN (3) ) AND p.m_primary = 'Y' ORDER BY s.m_order ASC, p.m_order ASC, s.m_name ASC; SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '287' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '291' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '297' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '290' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '309' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '228' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '54' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '93' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '98' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '178' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '174' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '221' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '76' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '224' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '222' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '227' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '205' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '51' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '206' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '52' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '53' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '154' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '147' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '149' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '169' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
SELECT m_id, m_finish FROM s_products WHERE m_subcategory = '170' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;
