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

Question

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 · Answer

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

Nic Brough -Adaptavist- · Answer

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

Joe_89j7ep · Answer

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