SQL QUERY FOR LINKED TYPE for Project ABC

[Swaroop vj]

Can any one   help  me how to write SQL Query for Issue type Bug in Project . Feilds should be like below

Key

Summary

Component

Assignee

FixVersion

Cycle

Linked Issues with Link Type (This is the most important and I can’t get through the UI)

Test Status

[Alex Suslin]

I don't know what is Cycle and Test Status... probably some of the custom addon fields...

But you may start with the MS SQL below

select
      CONCAT(P.pkey,'-',JI.issuenum) as 'Key'
    , JI.SUMMARY as 'Summary'
    ,(SELECT Stuff(
        (SELECT ',' + C.cname FROM component C
         INNER JOIN nodeassociation NA ON JI.ID = na.SOURCE_NODE_ID AND NA.SINK_NODE_ID = C.ID
         FOR XML PATH('')
        ), 1,1, '')) as Components
    , JI.ASSIGNEE as 'Assignee'
    ,(SELECT Stuff(
        (SELECT ',' + PV.vname FROM  projectversion PV
         INNER JOIN nodeassociation NA ON JI.ID = na.SOURCE_NODE_ID AND NA.SINK_NODE_ID = PV.ID
         FOR XML PATH('')
        ), 1,1, '')) as Components
    , IT.pname as 'IssueType'
    , ILT.LINKNAME as 'LinkName'   
FROM dbo.jiraissue JI
INNER JOIN dbo.project P on JI.PROJECT = P.ID
INNER JOIN dbo.issuetype IT ON IT.ID = JI.issuetype
INNER JOIN dbo.issuelink IL ON IL.SOURCE = JI.ID
INNER JOIN dbo.issuelinktype ILT ON ILT.ID = IL.LINKTYPE
WHERE IT.pname = 'Bug' AND ILT.LINKNAME = 'Duplicate'

Also check: https://developer.atlassian.com/jiradev/jira-platform/jira-architecture/database-schema

to understand the JIRA scheme

[Alex Suslin]

I don't know what is Cycle and Test Status... probably some of the custom addon fields...

But you may start with the MS SQL below

select 
	  CONCAT(P.pkey,'-',JI.issuenum) as 'Key'
	, JI.SUMMARY as 'Summary'
	,(SELECT Stuff(
		(SELECT ',' + C.cname FROM component C 
		 INNER JOIN nodeassociation NA ON JI.ID = na.SOURCE_NODE_ID AND NA.SINK_NODE_ID = C.ID 
		 FOR XML PATH('')
		), 1,1, '')) as Components
	, JI.ASSIGNEE as 'Assignee'
	,(SELECT Stuff(
		(SELECT ',' + PV.vname FROM  projectversion PV 
		 INNER JOIN nodeassociation NA ON JI.ID = na.SOURCE_NODE_ID AND NA.SINK_NODE_ID = PV.ID 
		 FOR XML PATH('')
		), 1,1, '')) as Components
	, IT.pname as 'IssueType'
	, ILT.LINKNAME as 'LinkName'	
FROM dbo.jiraissue JI
INNER JOIN dbo.project P on JI.PROJECT = P.ID
INNER JOIN dbo.issuetype IT ON IT.ID = JI.issuetype
INNER JOIN dbo.issuelink IL ON IL.SOURCE = JI.ID
INNER JOIN dbo.issuelinktype ILT ON ILT.ID = IL.LINKTYPE
WHERE IT.pname = 'Bug' AND ILT.LINKNAME = 'Duplicate'

[Vasiliy Zverev]

Here is SQL statement to get linked issues

select	*
from	jiraissue
join	issuelink	
	join	issuelinktype	
	on		issuelink.LINKTYPE = issuelinktype.ID
	and		issuelinktype.LINKNAME = 'Cloners' 
on	jiraissue.ID = issuelink.SOURCE