nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

MySQL query to list of all issues by issue type including customfields value

rozuan

Hi All,

I need help to list all of the issues details on jira by issue type in mysql query including customfields.

Anyone have experienced this? Please help me by giving some example.

I would like to display customfield option value. For example I have a few custom field such as Type, Contract, Source and Category. How to display these all including basic from jiraissue table?

I have my own dummy sql statement but I dont know how to include the customfields.

SELECT jiraissue.id,jiraissue.pkey,project.pname AS project,jiraissue.summary,issuetype.pname AS issuetype,
issuestatus.pname AS status,jiraissue.assignee,jiraissue.reporter,jiraissue.created,
jiraissue.resolutiondate,jiraissue.duedate,jiraissue.description,customfieldvalue.customfield,
customfieldoption.customvalue, customfield.cfname

FROM jiraissue,project,issuetype,resolution,issuestatus,customfieldvalue,customfield,customfieldoption

WHERE jiraissue.project=project.ID AND jiraissue.issuetype=issuetype.ID 
AND jiraissue.resolution=resolution.ID AND jiraissue.issuestatus=issuestatus.ID 
AND jiraissue.issuetype IN (43,40,37,39)

Thank you.

Accepted answers

All comments

Udo Brand

To see the values of the customfields in your issues you need to take a look at customfieldvalue.stringvalue, customfieldvalue.numbervalue, etc. depending on the field type.

No need to join customfieldoption in your query (this table contains all options of a customfield like select list, multi check box, etc.)

rozuan

I want the result should be like this. Maybe you can help me with the query?

thank you

Udo Brand

This is not that simple since you would need to join customfieldvalue for each customfield you want to display. I don't know your fields and frankly I currently don't have that time.

Why don't you use JQL and export that data?

By the way, you need to outerjoin resolution because there could be unresolved issues.

rozuan

If I could, I would use the JQL. However, as you can see in the attachment, the duedate field didnt support datetime format. thats why I need to export using sql query. Apreciate if you could help me to do some example on four of my customfields only to display the value of each customfield. I have done a few query and cannot get the right result which i dont know where in the database (which field and table) that store the value of selected customfield. For example (refer my attachment above), I have four custom fields and each of them had multiple option for client to select during creating the issue. When they select the value for each of the customfield, where is it store in database? I found only list of all value for each customfield only and not the value that client select. Any idea?

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

sql

jira

jql

mysql