I need an SQL query to find all the stories, tasks, sub-tasks under an EPIC.

[Manish]

I'm working on some usecase where my backend data is Jira database. I need an SQL query to give me all the stories, tasks, sub-tasks under an EPIC.

Can someone help me?

Thanks

[Fabio Racobaldo _Herzum_]

Hi @Manish and welcome,

please try the following SQL :

SELECT JIC.summary as child_summary,
concat(concat(PJP.pkey,'-'),JIP.issuenum) as parent_key, JIP.ID as parent_issue_id,
concat(concat(PJC.pkey,'-'),JIC.issuenum) as child_key, JIC.ID as child_issue_id,
ILT.linkname
FROM
JIRAISSUE JIP
INNER JOIN ISSUELINK IL ON IL.SOURCE = JIP.ID
INNER JOIN ISSUELINKTYPE ILT ON ILT.ID = IL.LINKTYPE
INNER JOIN JIRAISSUE JIC ON JIC.ID = IL.DESTINATION
INNER JOIN PROJECT PJP ON PJP.ID = JIP.PROJECT
INNER JOIN PROJECT PJC ON PJC.ID = JIC.PROJECT
INNER JOIN ISSUETYPE IT ON IT.ID = JIP.ISSUETYPE
where IT.PNAME = 'Epic' AND PJP.pkey='YOUR-PROJECT-KEY' AND JIP.issuenum='YOUR-ISSUENUM';

Example : TEST-123

YOUR-PROJECT-KEY : TEST

YOUR-ISSUENUM : 123

Hope this helps,

Fabio