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

Question

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:

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

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

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

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

mwarton · Answer

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,

Mehmet Kazgan_qriny9 · Answer

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.

daniel santigosa · Answer

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:

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

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

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

I hope this help you