SELECT
  ia.descriptor_id, descriptor_name,
  MAX(pkg_override_id) AS pkg_id, MAX(version_type_id) AS version,
  CASE WHEN MAX(version_type_id) > 1 THEN 1 ELSE 0 END AS overridden,
  CONCAT('[',
    SUBSTRING_INDEX(
      GROUP_CONCAT(
        CASE
          WHEN cat_override_id IS NOT NULL AND cat_override_id > 0
            THEN CONCAT('{"',ia.item_id,'" : [ {"iname" : "',item_name,'"} , {"icat" : "',
                CASE
                  WHEN cat_override_id IS NOT NULL OR cat_override_id = 0
                    THEN cat_override_id
                  ELSE -1
                END,
              '"} ] }')
          WHEN cat_override_id = 0
            THEN CONCAT('{"',ia.item_id,'" : [ {"iname" : "',item_name,'"} , {"icat" : "', cat_override_id,'"} ] }///')
          WHEN (pkg_override_id IS NOT NULL or pkg_override_id > 0) AND ia.version_type_id > 3
            THEN CONCAT('{"',ia.item_id,'" : "',item_name,'"}///')
          WHEN pkg_override_id IS NULL or pkg_override_id <= 0
            THEN CONCAT('{"',ia.item_id,'" : "',item_name,'"}  ')
          ELSE 0
        END
        ORDER BY ia.version_type_id DESC
        SEPARATOR ',')
      ,'///',1)
    ,']') AS powerlist

FROM
  type_item_associations pa,
  type_items itm,
  type_descriptors dd

WHERE
  (pkg_override_id IS NULL or pkg_override_id = 0 OR pkg_override_id = 261)
  AND itm.item_id = ia.item_id AND dd.descriptor_id = ia.descriptor_id
  AND ia.version_type_id IN(1,4,5)

GROUP BY descriptor_id

HAVING descriptor_id IN (26,6,23,27,19,3)

ORDER BY descriptor_name