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_new = 'Y' AND p.m_specification = '' 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 = '334' 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 = '320' 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 = '337' 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 = '337' 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 = '337' 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 = '347' 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 = '347' 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 = '338' 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 = '339' 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 = '340' 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 = '345' 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 = '335' 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 = '336' 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 = '326' 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 = '348' 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 = '342' 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 = '343' 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 = '344' 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 = '327' 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 = '330' 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 = '331' 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 = '321' 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 = '322' 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 = '332' 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 = '333' 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 = '346' 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 = '323' 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 = '324' 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 = '328' 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 = '325' 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 = '329' 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 = '349' 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 = '299' 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 = '310' 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 = '316' 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 = '316' 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 = '317' 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 = '318' 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 = '253' 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 = '285' 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 = '255' 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 = '286' 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 = '156' 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 = '250' 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 = '249' 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 = '144' 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 = '22' 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 = '12' 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 = '135' ORDER BY CASE m_primary WHEN 'Y' THEN '1' ELSE '999' END asc, m_order ASC, m_name ASC;