Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Things NOT to do with MySQL, generally speaking
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.