nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Alok Band

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.

Accepted answers

JamieA

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'

All comments

JamieA

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

Nice thanks:)

JamieA

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

Alok Band

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;

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

sql

jira