How can you use SQL to find the component id for an issue in the oracle tables?

[Amber McCallister]

I see a table called component which gives me the component IDs for a project (19130). In the JIRAISSUE table there is a component column but they are all null. I need to find the link between the issue and the component table.

[Andrey Kuzmin · Accepted solution]

It's going to be something like this:

SELECT ji.issuenum, na.sink_node_id component_id
FROM   jiraissue ji
JOIN   nodeassociation na
ON     na.source_node_id = ji.id
       AND na.sink_node_entity = 'Component'
       AND na.source_node_entity = 'Issue'
WHERE  ji.project = 19130

[Amihai Markovitz]

Hi,

I am using this tip but I still don't get the Component value, any other idea?

[Nic Brough _Adaptavist_]

The component column is a hangover from older ways of doing 

Have a look in "nodeassociation" - I can't remember the name of the lines you need, but one column will have the JIRA issue id, and another the component id, with the third column containing something like "componentlink"

[Nic Brough _Adaptavist_]

Give Andrey the points :-)

[Amber McCallister]

Nic and Andrey,

I wanted to thank you both for all of your help with this.

Thanks,

Amber

[Bhagya M S]

You can use the below query to fetch the components for a particular issue in JIRA

Please specify the valid jira issue key for below query:

note: in my case jira issue key is 'TEST-171'

SELECT
(case when (LENGTH(CM.CNAME)>0) then CM.CNAME else ' ' end) AS COMPONENT
FROM JIRAISSUE JI, Project P, COMPONENT CM, NODEASSOCIATION NA  where CM.PROJECT=P.ID AND JI.PROJECT = P.ID AND NA.SOURCE_NODE_ID=JI.ID AND NA.SINK_NODE_ID=CM.ID AND P.PKEY||'-'||JI.ISSUENUM= 'TEST-171'

[Gregory Riccardi]

Hey Guys this is a big help, I am looking for something a little more simple however.

I would like a list of all component names in a project. 

Any ideas?

[Nic Brough _Adaptavist_]

Search the component table where project = the id of the project.