Query to retrieve the JIRA tasks associated with a project, along with the component they are tied to

Shankar Rao Pal

Hi,

Currently, i am using below query to retrieve the list of all JIRA tasks associated with a project (Project key = Test) based on the project key. Below query returns the expected data. However I am looking to modify the query so as to retrieve 'Component' information associated with the JIRA tasks.

SELECT JI.pkey AS Issue_ID, CG.CREATED AS Modified, CG.AUTHOR AS Modified_By, CI.FIELD AS Field_Modified, CI.NEWSTRING AS New_Value FROM jiraissue JI, changegroup CG, changeitem CI WHERE JI.ID=CG.ISSUEID AND CG.ID=CI.GROUPID AND JI.PKEY like '%Test-%' AND CI.FIELD = 'Stage' ORDER BY JI.ID;

Could you please let me know what changes are to be made to above query so as to retrieve the component information of all JIRA tasks, for a particular project?

Thanks,

Shankar

Nic Brough -Adaptavist-

You need to read nodeassociation (for the links to components) and the components table (for the data about the component)

Include something like this in your query (which is a copy and paste from the docs - https://confluence.atlassian.com/display/JIRA041/Database+Schema#DatabaseSchema-Componentsandversions )

select * from component where id in (
    select SINK_NODE_ID from nodeassociation 

           where ASSOCIATION_TYPE='IssueComponent' 

           and SOURCE_NODE_ID=(
               select id from jiraissue where pkey='JRA-5351')
    );