List ALL statuses using SQL (including custom issue statuses)

[Lars Suffys]

Hi guys,

I'm a trainee and i have maybe a stupid question but i need help :)

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?

[Lars Suffys · Accepted solution]

I found the mistake myself, I joined on issuestatus.SEQUENCE = j.issuestatus

it should be: issuestatus.ID = j.issuestatus

[Nic Brough -Adaptavist-]

Dump the join, it's pointless.  Issuestatus is the list of status.

[Lars Suffys]

My full query is a bit more complicated.

SELECT concat(p.pkey,'-',j.issuenum) as Issuekey,j.SUMMARY as Summary,issuestatus.pname as Status,j.ASSIGNEE
FROM jiraissue as j
LEFT JOIN project as p ON p.id = j.PROJECT
LEFT JOIN cwd_user as u ON u.lower_user_name = j.REPORTER
LEFT JOIN issuestatus ON issuestatus.SEQUENCE =j.issuestatus
WHERE (SUBSTRING_INDEX(SUBSTR(u.lower_email_address, INSTR(u.lower_email_address, '@') + 1),'.',1))
LIKE (SUBSTRING_INDEX(SUBSTR("martin.boehme@communardo.at", INSTR("martin.boehme@communardo.at", '@') + 1),'.',1))

 With this query i want to list information about related issues who have a reporter with the same email domain as "martin.boehme@communardo.at"

This query works but in the Status field it gives NULL on certain issue status (issue statusses that are not automatically generated by JIRA)

[Lars Suffys]

I found the mistake, i joined on issuestatus.SEQUENCE = j.issuestatus.

It should be: issuestatus.ID = j.issuestatus