Hello @Jyotsna Chimakurthi
The issuelink table stores information about links between issues. However it references the issues by their unique numeric ID. It doesn't explicitly store the issue key.
To find the project in which the issue exists you need to cross reference the issuelink.source and issuelink.destination against the jiraissue.id field to find the issues.
However, the jiraissue table stores only the ID of the project, not the name or the key. But you can find the ID for the project in the UI from the Project Settings > Details page. The project ID will be included in the URL for that page.
Once you have the ID for the project you would use a query something like this:
select
pr1.pkey, ji1.issuenum, pr2.pkey, ji2.issuenum
from issuelink
left join jiraissue as "ji1" on issuelink.source = ji1.id
left join jiraissue as "ji2" on issuelink.destination = ji2.id
left join project as "pr1" on ji1.project = pr1.id
left join project as "pr2" on ji2.project = pr2.id
where
ji1.project=11101 or ji2.project=11101
This will give you output like this, showing the project key and issue number for each issue in a linked pair.
| project key 1 |
issue num 1 |
project key 2 |
issue num 2 |
| AA |
1 |
AA |
2 |
| AA |
3 |
BB |
1 |
| BB |
2 |
AA |
4 |
Since the project you care about could be either the source or destination issue in the linked pair simply taking the number of rows of output doesn't tell you how many individual issues in the project are part of a link.
You would need to export the output, get all the data into just two columns listing the project keys and issue numbers for each issue, then sort the output so you could eliminate the issues that are in other projects and eliminate the duplicate issues from the project against which you want to report.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.