List ALL statuses using SQL (including custom issue statuses)

[Lars Suffys]

Hi,

I need an sql query that lists all status names, including the ones not automatically generated by JIRA

I have this:

SELECT issuestatus.pname
FROM jiraissue as j
LEFT JOIN issuestatus ON j.issuestatus = issuestatus.SEQUENCE

This works for statusses built in JIRA but not the ones i created myself like "Waiting for customer". These custom made statusses list as NULL

How can i list ALL the statusses?

[Andy Heinzer · Accepted solution]

The sequence field from the issuestatus table is not actually the correct field to join on here. Instead it should be joined to the id field in that table.

Perhaps this might help:

select (p.pkey || '-' || ji.issuenum) as issuekey, issu.pname as status
from jiraissue ji 
join project p on ji.PROJECT = p.ID
join issuestatus issu on ji.issuestatus = issu.id;

This query pulls from the project table as well to give you the specific issue key in Jira and its status.   That query shows all the issues in Jira and their status.

If you just want to see the possible status values in Jira right now, you could do a

select pname from issuestatus;

And that will be just a list of the statuses in Jira, both system and user created.

[Lars Suffys]

I eventually worked it out on my own. I figured out the join should be on the ID field instead of the sequence field as you suggested.

Anyways, thanks for your answer. Hope this will help someone else some day.