SQL: Finding story or epic for issue

Question

I'm querying the database (MySQL) using raw SQL. I want a report that is "time logged against each story/epic in date range"

I can get "time logged against each issue in date range" by joining worklog and jiraissue.

However, I don't know how to map an issueid to a story or epic. Where in the database is this stored, and how do I get it back?

JamieA · Accepted Answer

You need to join jiraissue to IssueLink, then back to jiraissue, on source and destination to jiraissue.id.

I find that these queries quickly get out of hand, and it's probably worth writing a plugin. That way you can also give your users access to the data without letting them query the db directly.