CODEWITHSUNDEEP
sqlpostgresqljira
Jose
Jose

Reputation: 51

JIRA - SQL Find all Subtasks given a parent issue number

The main table on the jira database has no information about subtasks.

I'm trying to find all subtasks from a given parent issue using the SQL and not the JIRA web interface.

Where is this information located?

Upvotes: 1

Views: 3006

Answers (3)

UUDDLRLRBA
UUDDLRLRBA

Reputation: 1

The issuelink table is the table to use but you must realize that the table is also used for other task relationships as well (blocks, clones, duplicates, etc) so that means if you are only looking for subtasks you must indicate the specific link type you are looking for. Thus the proper query would be:

SELECT jiraschema.issuelink.DESTINATION AS childID
FROM jiraschema.issuelink
    INNER JOIN jiraschema.issuelinktype
    ON jiraschema.issuelink.LINKTYPE = jiraschema.issuelinktype.ID
WHERE jiraschema.issuelinktype.pstyle = 'jira_subtask'
    jiraschema.issuelink.SOURCE = [parent_issue_id]

Upvotes: 0

RamChandra Ali
RamChandra Ali

Reputation: 41

I found that not only:

  • Where source is the jiraissue.id value of the parent, and destination is the jiraissue.id of the children.

There is viable variant exist too, when:

  • Where destination is the jiraissue.id value of the parent, and source is the jiraissue.id of the children.

So, that's means thats links can have two directions.

Upvotes: 0

Jose
Jose

Reputation: 51

In Jira, the parent issue, child issue/subtask relationship is stored in the table issuelink.

The issue link table has the following fields:

| id | linktype | source | destination | sequence |

  • Where source is the jiraissue.id value of the parent, and destination is the jiraissue.id of the children.

The following query will return the children of the parent issue:

SELECT destination AS children FROM issuelink WHERE source=XXX;

  • where XXX is your parent jiraissue.id number.

Upvotes: 2

Related Questions