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.
Community moderators have prevented the ability to post new answers.
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.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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%'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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....
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.