How to query Jira in MSSQL to get Issues that don't have a specific component or have no components?

[Joe]

I am trying to get all the open issues in a project that don't have a specific component attached to it. However when I do this, it does not pull back any issues that have no components at all. Therefore, an issue must have at least one component in order to come back in the results. How can I make it so that this is not a requirement?

What I am doing currently is gathering a distinct list of all the open issues and then selecting the issues from that list that do not have the component "Printed". However like I said, this does not incorporate issues with no components at all.

This is in Microsoft SQL to be clear.

Thanks

[Korsten Bezuidenhout]

Hi @Joe ,

What I have done was to create a distinct list of all projects with the issue numbers:

Select 

concat(concat(p.pkey,'-'),i.issuenum) as Issue_key, i.*

From jiraissue as i

inner join projects as p

on i.project = p.id

Once you have that - then use it as you main table and left join your component code output to it.

It will return NULL when there is no component for that issue specifically.

Hope it helps

[Dan Breyen]

I apologize, I don't have a database where I have SQL access to fully test, but wouldn't your query be something like ((component <> 'PRINTED') or (component is NULL))

depending on how the db treats 'no component' it could be an Empty string instead, so you would use (component = "") instead.

Hope that helps.

[Joe]

Thanks. The problem is that SQL with Jira reads each component on an issue as a separate row. So if a Jira issue has no components, it does not appear in the results

[Marco Brundel]

Hi @Joe ,

When you use JQL you can try this:

component != Printed OR component IS EMPTY

Regards, Marco 

[Joe]

Hi. This is in Microsoft SQL unfortunately, not JQL. I have updated the post. Thanks

[Nic Brough -Adaptavist-]

I recommend that you stop using SQL to do this sort of reporting.

SQL can't report on what is not there, which is what you have here.  The Jira database really isn't designed for reporting and SQL is the worst possible way to read it.

As @Marco Brundel points out, JQL can answer the question, and it is a lot easier to use than SQL