SELECT LEFT(itemcode,12) AS itemcode, LOWER(CONCAT(CASE SUBSTRING(itemcode,5,1)
    WHEN 'M' THEN 'Men''s'
    WHEN 'W' THEN 'Women''s'
    WHEN 'B' THEN 'Boy''s'
    ELSE ''
  END,' ', CASE SUBSTRING(itemcode,2,1)
    WHEN 'A' THEN subgroup
    WHEN 'B' THEN subgroup
    WHEN 'C' THEN CONCAT(subgroup,' ', itemname_real)
    WHEN 'D' THEN CONCAT(subgroup,' ', itemname_real)
    WHEN 'E' THEN CONCAT(subgroup,' ', itemname_real)
    WHEN 'F' THEN CONCAT(subgroup,' ', itemname_real)
    WHEN 'G' THEN CONCAT(subgroup,' ', itemname_real)
    WHEN 'H' THEN CONCAT(subgroup,' ', itemname_real)
  END)) AS itemname, retailprice, itemimage, itemcolor_value, itemcolor_name, LOWER(search_color) AS search_color, 
              instore, promoprice, retailprice, promo_datestart, promo_dateend, CASE 
            WHEN promo_datestart <= NOW() AND promo_dateend >= NOW() AND promoprice > 0 THEN promoprice
            WHEN promo_datestart IS NULL AND promo_dateend IS NULL AND promoprice > 0 THEN promoprice
            ELSE retailprice
          END AS pricing, SUM(item_stock) AS stock, MIN(RIGHT(itemcode,3)) AS size, LOWER(theme) AS theme 
          FROM article 
          WHERE is_shown = 1 AND LEFT(search_tag, LENGTH('women')) = 'women'  AND LOCATE(CONCAT('|',itemcolor_name ,'|'), '|PINKRED|YELLOWLIGHT BLUE|BLACK|') > 0  AND LOCATE(CONCAT('|',RIGHT(itemcode,3),'|'), '|005|') > 0  GROUP BY LEFT(itemcode,12)  ORDER BY create_date DESC LIMIT 12