Skip to content

Instantly share code, notes, and snippets.

@jrtaylor-com

jrtaylor-com/Charlie Paragon Reports

Created May 25, 2020 20:52
Show Gist options
  • Save jrtaylor-com/9c161e332a3a39514cc4bf5155f53de5 to your computer and use it in GitHub Desktop.
Save jrtaylor-com/9c161e332a3a39514cc4bf5155f53de5 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Charlie Paragon Reports
SELECT
om.mundane_id AS ORKID,
om.persona,
COUNT(*) AS numParagons,
(SELECT COUNT(*) FROM ork_awards sub WHERE sub.mundane_id = om.mundane_id AND sub.`kingdomaward_id` IN(SELECT kingdomaward_id FROM ork_kingdomaward subk WHERE subk.award_id = 27)) as ootwCnt
FROM
ork_awards oa
LEFT JOIN ork_kingdomaward oka ON oka.kingdomaward_id = oa.kingdomaward_id
LEFT JOIN ork_award oaa ON oaa.award_id = oka.award_id
LEFT JOIN ork_mundane om ON om.mundane_id = oa.mundane_id
WHERE
om.mundane_id > 0
AND oka.award_id IN(37, 38, 39, 40, 41, 42, 43, 44, 46, 49, 50, 51)
GROUP BY om.mundane_id
HAVING numParagons >= 4
ORDER BY om.persona ASC
-----------------
SELECT
om.mundane_id AS ORKID,
om.persona,
COUNT(*) AS numParagons,
(SELECT COUNT(*) FROM ork_awards sub WHERE sub.mundane_id = om.mundane_id AND sub.`kingdomaward_id` IN(SELECT kingdomaward_id FROM ork_kingdomaward subk WHERE subk.award_id = 27)) as ootwCnt
FROM
ork_awards oa
LEFT JOIN ork_kingdomaward oka ON oka.kingdomaward_id = oa.kingdomaward_id
LEFT JOIN ork_award oaa ON oaa.award_id = oka.award_id
LEFT JOIN ork_mundane om ON om.mundane_id = oa.mundane_id
WHERE
om.mundane_id > 0
AND oka.award_id IN(37, 38, 39, 40, 41, 42, 43, 44, 46, 49, 50, 51)
GROUP BY om.mundane_id
HAVING numParagons = 3
ORDER BY om.persona ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment