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?

[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'

[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 :-)

[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.

[ban yige]

Excuse me, does this project still exist ? I cannot find it. 

[Nic Brough -Adaptavist-]

What project?

[ban yige]

 the id of the project

[Nic Brough -Adaptavist-]

I'm afraid that does not explain your question.  What project are you asking about?

Actually, what are you trying to do here?  That might be easier to tell us than explain what "project that might not exist" is

[Amber McCallister]

Nic and Andrey,

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

Thanks,

Amber