View issue details along with linked issue details

I have an issue FR1 which has a custom field with values TCD1, TCD2 & TCD3.  

I have another issue DT1 which has a custom field with value TCD2.  

Now, I need a query or gadget to view following  

DT1, TCD2, FR1

8 answers

A similar use case (linking via custom field but the custom field contains an issue key rather than a generic value) is being resolved by a lot of users with SQL for JIRA. HOWEVER, your use case is MUCH MORE interesting and COMPLEX, and fortunately it is also resolvable with SQL for JIRA. BUT you have to have a MEDIUM-ADVANCED  SQL user SKILL because the SQL is NOT TRIVIAL wink at the time that is one of the most beatiful SQL queries you might want to create and a perfect use case to show the power of SQL for JIRA to the rest of the World:

These would be the major steps to build your SQL for JIRA query:

A) Given a issue with key 1 for the project PROJ_A  (PROJ_A-1)  you have to join the ISSUECUSTOMFIELD table to get the custom field (ie: named "CF_X")  

select 'CF_X = ' || CF_X  as "JQL", i1.key as "KEY", CF_X as "CF_X" from issues i1 inner join issuecustomfields cf1 on cf1.issueid = i1.id where i1.key='PROJ_A-1'

IMPORTANT: Note please that the query above returns a JIRA JQL as the first column by concatenating the 'CF_X = ' string to the CF_X value. I.e: If you perform the query in the built-in SQL console you would get a string similar to:

CF_X = XXX

guessing that the CF_X custom field value is XXX (of course, you would have to use double quotes for no one-word strings: CF_X = "XXX"

B) Now, you have to use the query above as INPUT table for a JOIN (this is the trick!). You could think on the query above as a sort of virtual table named "V" for instance with three columns (JQL, KEY, CF_X).


select v.KEY, v.CF_X, i2.key from (

select 'CF_X = ' || CF_X  as "JQL", i1.key as "KEY", CF_X as "CF_X" from issues i1 right join issuecustomfields cf1 on cf1.issueid = i1.id where i1.key='PROJ_A-1'

) v inner join issues i2 on i2.JQL = v.JQL

Please take a minute of your time to understand how the query above will be resolved by the H2 database engine: first, the "virtual" V table will be created on the fly by performing the embedded query (A) which will return a JIRA JQL. The JQL query will be used as input to populate the joined ISSUES i2 table, so you are getting all the issues from JIRA having the CF_X with XXX value via JQL. 

C) Next step is joining the ISSUECUSTOMFIELD table again (cf2) to get the custom fields for the issues (B) and filter by the CF_X condition:

select v.KEY, v.CF_X, i2.key from (

select 'CF_X = ' || CF_X  as "JQL", i1.key as "KEY", CF_X as "CF_X" from issues i1 inner join issuecustomfields cf1 on cf1.issueid = i1.id where i1.key='PROJ_A-1'

) v right join issues i2 on i2.JQL = v.JQL

inner join ISSUECUSTOMFIELDS cf2 on cf2.issueid = i2.id WHERE cf2.CF_X = v.CF_X

D) Finally, you might want to discard the "reflexive" solution of the PROJ_A-1 issue joined to itself. In other words, you don't want the

PROJ_A-1, XXX, PROJ_A-1 

result.

You can do it via SQL by adding a new condition to WHERE clause in the query above:

AND i2.key != v.KEY

Or more interestingly (and advanced, it will also perform better) by excluding it from the JQL in the (A) query:

select 'CF_X = ' || CF_X  || ' AND key != ' ||  i.key as "JQL", i1.key as "KEY", CF_X as "CF_X" from issues i1 inner join issuecustomfields cf1 on cf1.issueid = i1.id where i1.key='PROJ_A-1'


Voila! smile

Disclaimer: I've don't tested it, so it might have typo errors, etc.

And of course, there are also some alternatives to visualize those data (ie: via gadget, i.e: via custom field and much more are coming soon). 

You could also transform the query above into a new JQL query by using the built-in sql function to get the "linked" issues via custom field value and use it as quick filter for your Agile backlog.

Thanks Pablo. Hope this will help me to solve the issue.

I'll check these and update you. 

Steven Behnke Community Champion May 28, 2016

Nice!

0 vote
Steven Behnke Community Champion May 26, 2016

Can you draw a simple diagram? I'm not clear...

My mind curled up a bit there

Hi Steven,

I have two issues in JIRA and there are values in the customer fields to relate both the issues. Now I need to fetch details from both the issues using this relationship. There is no links created between these two issues in JIRA. If Link is required to fetch details from both the issues then I may create it. 

Issue #1 :

Key = FR1

Custom Field x : TCD1, TCD2, TCD3

 Issue #2:

Key = DT1

Customer Field y : TCD2

 I want to view below:

 Issue#2.Key, Issue#2.Custom Field y, Issue#1.Key

 Condition may be Issue#1.Customer Field x contains Issue#2.Customer Field y

Let know if I'm still not clear. Sorry, I'm not able to include image.

Thanks. 

0 vote

So you're saying you want to link issues without linking them?  Using a custom field instead of a link?  Is that right?

My main requirement is to view/fetch details from two different issues in JIRA using link or this kind of relationship (i.e. matching values in custom fields). If this can be done without link it will be great.

Thanks.

0 vote

Links will appear on the issues you've linked from, so that should solve the problem for you.

Matching fields in custom fields could be done, but it requires code, and if you have a lot of issues with a particular value, you're going to have performance issues.  

You shouldn't be trying to do vast numbers of links of either type in an issue really - that's what searching is for.

Yes, the link will appear on the issues linked from.

But as a report if I want to see all the issues in a project and the list of issues linked to that issue, then I have to fetch details from multiple issues (at least two different issues). Is it possible, something like relational database query?

Thanks. 

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Posted 14 hours ago in Jira

We want to know what Jira Service Desk apps you're using!

Hi Community! My name is Amir and I’m on the Jira Service Desk product marketing team at Atlassian. Our team would love to understand how you’re leveraging our ecosystem for Jira Service Desk. Wha...

38 views 0 5
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you