SQL query for Project name, category and workflow

Question

Hi

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

Fabio Racobaldo _Catworkx_ · Answer

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

M Amine · Answer

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