How do I query the parent issue id of a subtask?

[Velina Urdova]

Hello,

I am writing a simple query against the Jira Database, but I cannot get the parent issue id of the issues. I tried including customfieldvalue.parentkey, but it is NULL for all the subtasks. Does anyone know what the actual parent key id is?

Thanks,

Velina Urdova

[Jobin Kuruvilla [Adaptavist] · Accepted solution]

You should be looking at issuelink table. Subtasks will have a link to parent issue with link type as 'jira_subtask_link'. You can find the id of linktypes from issuelinktype table.

[Velina Urdova]

Thank you very much, Jobin!

[DIOUF Abdou Karim]

Hello all,

I came across this thread because I am trying to get the Parent link directly out of jira database by querying in SQL. I am not able to find it. Could you please share your sql code that you wrote to get it. Thank you in advance.

Here is the Sql query I am writing :

 SELECT P.PNAME, I.ID, I.SUMMARY, ILT.LINKNAME FROM ISSUELINK IL
    LEFT OUTER JOIN ISSUELINKTYPE ILT ON IL.LINKTYPE=ILT.ID
    LEFT OUTER JOIN JIRAISSUE I ON IL.SOURCE=I.ID
    LEFT OUTER JOIN PROJECT P ON I.PROJECT=P.ID
    WHERE ILT.LINKNAME='jira_subtask_link'
      AND P.PNAME='XXXX'

[Hung Nguyen]

I came across this too, and I saw that I had to use the following SQL instead

SELECT I.ID as "Sub-Task ID", P.Project_Key||'-'||I.Issuenum As "Sub-Task Key", I.SUMMARY as "Sub-task Summary",
P.Project_Key||'-'|| PI.Issuenum As "Parent Key"
FROM JIRAISSUE I
JOIN JIRAdbuser.IssueType IT on IT.ID = I.issueType
JOIN Project_Key P ON I.PROJECT=P.PROJECT_ID
LEFT OUTER JOIN ISSUELINK IL ON IL.DESTINATION=I.ID
JOIN ISSUELINKTYPE ILT ON IL.LINKTYPE=ILT.ID AND ILT.LINKNAME='jira_subtask_link'
JOIN JIRAISSUE PI ON IL.SOURCE=PI.ID
WHERE IT.PNAME = 'Sub-task'
AND P.PROJECT_KEY= 'XXXX'

Pretty much you have I for the Sub-Task, and PI for the Parent issue, hence you can get more information out of these if needed