SQL query to list all projects in category X

Ryan Aherne
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.
October 10, 2012

Hi,

I'm looking for a query to list all projects associated with a particular category.

Using:
MySQL 5.0.51a-24+lenny4 (debian)
JIRA v4.0#466

Any help appreciated,

Pug.

3 answers

1 accepted

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

5 votes
Answer accepted
Jobin Kuruvilla [Adaptavist]
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.
October 10, 2012
select * from jira.dbo.project where ID in (select SOURCE_NODE_ID from jira.dbo.nodeassociation 
  where ASSOCIATION_TYPE = 'ProjectCategory' and SINK_NODE_ID=10100);

where 10100 is the id of ProjectCategory.

Written for MSSQL. Adjust accordingly.

Ryan Aherne
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.
October 14, 2012

MySQL:

select * from project where ID in (select SOURCE_NODE_ID from nodeassociation    
where ASSOCIATION_TYPE = 'ProjectCategory' and SINK_NODE_ID=10100);

Thanks Jobin ;o)

5 votes
Nabil Sayegh
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.
May 21, 2013

Eeasier to read and with filter on category name instead id:

SELECT
	p.*
FROM
	project p
	JOIN nodeassociation na ON (p.ID = na.SOURCE_NODE_ID AND na.ASSOCIATION_TYPE = 'ProjectCategory')
	JOIN projectcategory pc ON (na.SINK_NODE_ID = pc.ID)
WHERE
	pc.cname like '%FooBar%'

0 votes
Pablo Beltran
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 14, 2016

Listing all the projects and their categories with SQL for JIRA (Database independent):

 

SELECT pc.name as "Category", p.name as "Project"

FROM projectcategorydefinitions pc RIGHT JOIN projects p ON p.category=pc.name

ORDER BY 1,2

Bud Manz March 24, 2016

Hi Pablo, I am not seeing a "projectcategorydefinitions" table in our install.  I wonder if it is a version difference? 

Pablo Beltran
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 24, 2016

Might you run the following query?

select table_name

from information_schema.tables

where table_schema='PUBLIC'

order by 1 asc

It should return 31 table names....

TAGS
AUG Leaders

Atlassian Community Events