How to list all projects in a specific Project Category

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

To list all projects that are configured under a specific Project Category through SQL query to the database. 

Solution

Run the following query:

select distinct p.pname, p.pkey 
	from project p join nodeassociation n 
		on p.id=n.source_node_id 
	where n.sink_node_entity='ProjectCategory' and
	n.sink_node_id=(select id from projectcategory where cname='Test') 
order by p.pname; 
  • Replace 'Test' with the project category name that you wish to list.

You may run an alternative query to fetch all projects with their Category:

select p.pname, p.pkey, pc.cname from project p
left join nodeassociation na on p.id=na.source_node_id and na.sink_node_entity='ProjectCategory'
left join projectcategory pc on na.sink_node_id = pc.id
group by p.pname, p.pkey, pc.cname;


(info) This is tested using PostgreSQL. Please modify it according to the needs of your database.

Last modified on Mar 21, 2024

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.