SQL join to show some native fileds along with some custom fields

In the past, it was easy to write an sql join statement and show pkey from the jiraissue table along with the custom value from the customfieldvalue table because these tables were related. Right now it is a little harder because there is a new table called customfieldoption that stores the customfield values. This table is not directly related to the jiraissue table and make is hard to create a join or subquery to retrieve the desired data. Here is an example of data I want to pull from the jira database:

+---------+-------------+-------------------+--------------+

| key | CUSTOMFIELD1 | CUSTOMFIELD2 | CUSTOMFIELD2 |

+---------+-------------+-------------------+--------------+

| abc-102 | 14812 | new york | xyz |

Please send me an sql statement I would use to show the above data.

2 answers

It depends on customfield you want to display. Data you want to display can be in customfieldoption or customfieldvalue.

To show something like your example you need for example:

SELECT 

    jiraissue.pkey,

    (select 

            customfieldvalue.stringvalue AS NUMBR

        FROM

            customfieldvalue

        WHERE

            customfieldvalue.issue = jiraissue.id

                AND customfieldvalue.customfield = 'xxxx'),

    (select 

            customfieldvalue.stringvalue AS CITY

        FROM

            customfieldvalue

        WHERE

            customfieldvalue.issue = jiraissue.id

                AND customfieldvalue.customfield = 'yyyy'),

    (select 

            customfieldoption.customvalue AS LETTER

        FROM

            customfieldvalue,

            customfieldoption

        WHERE

            customfieldvalue.issue = jiraissue.id

                AND customfieldvalue.customfield = customfieldoption.customfield

                AND customfieldvalue.customfield = 'zzzz')

FROM

    jiraissue


THIS QUERY DISPLAY:

+---------+-------------+-------------------+--------------+

| key | CUSTOMFIELD1(xxxx) | CUSTOMFIELD2(yyyy) | CUSTOMFIELD2(zzzz) |

+---------+-------------+-------------------+--------------+

| abc-102 | 14812 | new york | xyz |

I hope this help you

Daniel,

How could I get the following?:

customfield | Issue (with query1) | Issue (with query2)

customfield1| value for issue | value for the issue

customfield2| value for issue | value for the issue

Thanks.

Hi Julius,

Not sure exactly what you are doing, but if you install the Intelligent Reports plugin you could load this data into a table in a Word file with no programming involved, and use your data for whichever purpose from there.

Hope it helps,

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in Featured Groups

Tuesday tips & tricks: What is the Atlassian Community?

It's officially Tuesday, which means it's officially time for another tip to help you better navigate this space we call the Atlassian Community. 😄 I got a great question from community member, Sa...

152 views 6 8
View post

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