SQL Query : Retrieve a list of subtask with type of subtask from jira_issue

Alok Band May 17, 2012

I had posted same query some time back on the forum , however did not find appropriate response, thus trying again.

I need to find out how to get a list of subtask associated with a bug? I am not able to understand issuelinktype table for achieving this. If anyone has done this in past please share.

Thanks in advance.

1 answer

1 accepted

8 votes
Answer accepted
JamieA
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 17, 2012

Like this:

select p.pkey as parentkey, c.pkey as childkey, c.issuetype
from issuelink 
inner join jiraissue p on issuelink.source = p.id
inner join jiraissue c on issuelink.destination = c.id
inner join issuelinktype t on issuelink.linktype = t.id
where t.linkname = 'jira_subtask_link'

Alok Band May 17, 2012

Nice thanks:)

JamieA
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 17, 2012

Yep, points please! Ie mark correct and vote up, thanks.

Alok Band May 17, 2012

hi Jamie,

I built following query based on your response, however I need resolution,issuestatus for subtask not for parent key, can you please help :

here : (abc-123 is parent key), if def-456 is subtask, it should show resolution, label for the subtask not for parent key.

select p.pkey as parentkey,p.reporter,p.assignee,p.summary,p.created,
p.resolutiondate, c.pkey as certification_ticket, c.issuetype, r.pname as resolution
,istat.pname as issuestatus
,lb.label
from issuelink
inner join jiraissue p on issuelink.source = p.id
inner join jiraissue c on issuelink.destination = c.id
inner join issuelinktype t on issuelink.linktype = t.id
inner join resolution r on p.resolution = r.id
inner join issuestatus istat on p.issuestatus = istat.sequence
inner join label lb on p.id = lb.issue
where t.linkname = 'jira_subtask_link'
and p.pkey in ('abc-3')
and c.issuetype in ('13')
order by c.pkey,lb.label;

CEDRIC ZABEL
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 17, 2012

Just change your join of the resolution and label tables to join with the child instead of the parent.

select p.pkey as parentkey,p.reporter,p.assignee,p.summary,p.created,
p.resolutiondate, c.pkey as certification_ticket, c.issuetype, r.pname as resolution
,istat.pname as issuestatus
,lb.label
from issuelink
inner join jiraissue p on issuelink.source = p.id
inner join jiraissue c on issuelink.destination = c.id
inner join issuelinktype t on issuelink.linktype = t.id
inner join resolution r on c.resolution = r.id
inner join issuestatus istat on p.issuestatus = istat.sequence
inner join label lb on c.id = lb.issue
where t.linkname = 'jira_subtask_link'
and p.pkey in ('abc-3')
and c.issuetype in ('13')
order by c.pkey,lb.label;

Alok Band May 17, 2012

Thanks Cedric, How do I join issuestatus, should it be : c.issuestatus = istat.sequence?

JamieA
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 17, 2012

Join on issuestatus.id

Piyush_Annadate August 31, 2016

can someone help on this:

for 1706 issuenum, there are 3 subtask... this query returns 3 subtask but return data is commons(that's the 3 rows with 1706 data)

select *  from jiraissue jr
join
issuetype isty on isty.id=jr.issuetype
join
project prj on jr.project=prj.id
join
issuelink islk on islk.source=jr.id
where
isty.pname='Request'  and prj.pkey='JIRA' and jr.id = (
select jr.id from jiraissue jr, project prj
where
jr.project=prj.id and prj.pkey='JIRA' and jr.issuenum=1706
)
Punchi Rao May 19, 2018

Hello  ,

 

Please let me know how to get all sub tasks and tasks for a given project , using my sql data base ..

 

Thanks

Madhu Pesala March 19, 2020

Hi Punchi, 

 

Just checking, I am also having same requirement  ,did you find any solution for the SQL query to show list of sub tasks for the parent tickets

 

Regards

Madhu

Piyush_Annadate March 19, 2020
select p.pkey as parentkey, c.pkey as childkey, c.issuetype
from issuelink 
inner join jiraissue p on issuelink.source = p.id
inner join jiraissue c on issuelink.destination = c.id
inner join issuelinktype t on issuelink.linktype = t.id
where t.linkname = 'jira_subtask_link'

Suggest an answer

Log in or Sign up to answer