SQL query that returns a list of issues for a caterogie

Anthony Novo March 11, 2014

Hi,

I am looking for a query that returns all issues present from all projects in a category.

Thanks

3 answers

1 accepted

0 votes
Answer accepted
Fabio Racobaldo _Herzum_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 7, 2022

Hi @Anthony Novo ,

here your SQL query :

SELECT ji.*
FROM nodeassociation na
INNER JOIN projectcategory pc ON pc.ID=na.SINK_NODE_ID
INNER JOIN project p ON (na.SOURCE_NODE_ID=p.ID)
INNER JOIN jiraissue ji ON (ji.PROJECT=p.ID)
WHERE na.ASSOCIATION_TYPE='ProjectCategory' AND pc.cname='PROJECT_CATEGORY_NAME_HERE';

Hope this helps,

Fabio

Dan Breyen
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 6, 2023

@Anthony Novo did this resolve your issue?

0 votes
Henning Tietgens
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 11, 2014

In SQL the project category association is saved in the nodeassociation table (ASSOCIATION_TYPE='ProjectCategory').

0 votes
Henning Tietgens
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 11, 2014

In JQL (not SQL) you can write "category=MyCat" to select all issues in projects with category MyCat.

Anthony Novo March 11, 2014

Yes thanks, but i'am very interesting to the sql :D

I want the same display of the JQL but directly in the database with SQL.

Suggest an answer

Log in or Sign up to answer