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

[Julio Mugabe]

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.

[daniel santigosa]

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

[Mehmet Kazgan]

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.

[mwarton]

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,