JIRA SQL Query to fetch issues into hierarchy Initiatives, Epics, Stories/defects/tasks

[James Olivo]

Does anyone have a query that will fetch the issues and organize into hierarchy grouped by initiatives then epics then issues/stories? Even including epics without stories? I am trying to build some reporting and hygiene opportunities however epics without stories are missing from my counts.

I’m using Oracle SQL Dev DB.

I am also using the issuelink table with source and destination fields along with multiple joins with jiraissue.id and all left joins.

thanks!