nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to retrive list of bugs associated with a component in JIRA project (SQL Query)

Alok Band

I am trying to retrieve list of bugs associated with a component in JIRA project, can someone help me with query. I have been able to extract component from JIRA project, just need some help with list of bugs associated with component.

Thanks

Accepted answers

All comments

Adam Saint-Prix

Hi Alok,

Try using the following query in the advanced search (JQL)

project = ProjectName and issuetype = Bug and component = "Your Component"

If you don't care what the project is or if it's cross project, you can just do

issuetype = Bug and component = "Your Component"

Replace "Your Component" with the component name you'd like to search for...

Alok Band

Hi Adam,

Thank you for response, I will give it a try. However, I am looking for SQL query, I hope someone will post the query

Dieter

If the project key is 'JRA' , the name of the component is 'My Component and the name of the issue type is 'Bug' you could use this:

select jiraissue.* from jiraissue,project,issuetype,nodeassociation,component 
where 
component.cname = 'My Component' and 
project.pkey = 'JRA' and 
issuetype.pname = 'Bug' and
jiraissue.project = project.id and 
jiraissue.issuetype = issuetype.id and 
nodeassociation.association_type = 'IssueComponent' and
nodeassociation.source_node_entity = 'Issue'  and
nodeassociation.source_node_id = jiraissue.id  and
nodeassociation.sink_node_entity = 'Component'  and
nodeassociation.sink_node_id = component.id;

Dieter

Here is the solution to search for isssue with Component 'My component' in project with key 'JRA'

select jiraissue.* from jiraissue,project,issuetype,nodeassociation,component 
where 
component.cname = 'My component' and 
project.pkey = 'JRA' and 
issuetype.pname = 'Bug' and
jiraissue.project = project.id and 
jiraissue.issuetype = issuetype.id and 
nodeassociation.association_type = 'IssueComponent' and
nodeassociation.source_node_entity = 'Issue'  and
nodeassociation.source_node_id = jiraissue.id  and
nodeassociation.sink_node_entity = 'Component'  and
nodeassociation.sink_node_id = component.id

Alok Band

Dieter, Thanks for the response , appears query is returning 0 rows. I believe component is a unique value in entire JIRA project why do we need to associate project(.pkey) with it?

JamieA

The ID is unique but not the name. If you want to query by component ID alone you wouldn't need to join on project.

Adam Saint-Prix

Ah! Sorry. Misread the title of the post.

Dieter

If you have the component ID you could use this

select jiraissue.* from jiraissue,issuetype,nodeassociation,component 
where 
component.id = 12345 and 
issuetype.pname = 'Bug' and
jiraissue.issuetype = issuetype.id and 
nodeassociation.association_type = 'IssueComponent' and
nodeassociation.source_node_entity = 'Issue'  and
nodeassociation.source_node_id = jiraissue.id  and
nodeassociation.sink_node_entity = 'Component'  and
nodeassociation.sink_node_id = component.id

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

sql

jira