SQL query for Project name, category and workflow

[Ron Jacob]

Hi 

Can you tell me SQL query to get Project name, category and workflow name ?

[M Amine]

Hi Ron,

Try looking to this answer : here

• Mapping project with ProjectCategory : 

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

where 10100 is the id of 'ProjectCategory'. 

and this answer : here

• Mapping project with workflow : 

SELECT *

FROM PROJECT, NODEASSOCIATION, WORKFLOWSCHEME

WHERE PROJECT.ID = NODEASSOCIATION.SOURCE_NODE_ID

AND NODEASSOCIATION.SINK_NODE_ID = WORKFLOWSCHEME.ID

AND PROJECT.ID = '11630'

where 11630 is the id of the project.

cheers 

[Fabio Racobaldo _Herzum_]

Hi @Ron Jacob ,

here your SQL :

select ji.* 
from nodeassociation na 
inner join project p on (p.ID = na.SOURCE_NODE_ID)
inner join projectcategory pc on (pc.ID = na.SINK_NODE_ID)
inner join jiraissue ji on (ji.PROJECT = p.ID)
where na.ASSOCIATION_TYPE='ProjectCategory' and na.SOURCE_NODE_ENTITY='Project'
and pc.cname='YOUR PROJECT CATEGORY NAME HERE';

Hope this helps,

Fabio