IssueLabels in SQL+JQL query

Sher J

Hi,

I have written a query to link defects to the story they block.  I am trying to display the labels but when I do that, it then only shows the issues that have labels.  I have even tried adding to my select where the label is empty or the label is not empty with no change in results.  I am sure it is a simple error but I just can't see it.  If I remove any reference to labels, I get all issues as expected.  Query below. 

SELECT

distinct link(jql.issue, 'blank') AS "Story",

issueStatus(jql.issue) AS "Story Status",

link(jql2.issue, 'blank') AS "Defect",

Summary (jql2.issue) AS Summary,

issueStatus(jql2.issue) AS "Defect Status",

l.label AS "Label",

cfv.value  as "Severity",

Assignee (jql2.issue) AS "Assignee",

Created (jql2.issue) as "Created"

 

 

FROM

JQL

JOIN issuelinks d ON d.issueid = jql.issueid

JOIN issuelabels e ON e.issueid = jql.issueid

JOIN JQL JQL2 ON jql2.issueid = d.childissueid

join issueCustomFieldValues cfv on cfv.issueid = jql2.issueid

join issueCustomFielddefinitions CF on CF.Id = CFV.CustomFieldID

join issueCustomFieldOptions CFO on CF.Id  = CFO.CustomFieldID

JOIN ISSUES i ON i.id = jql2.issueid

join issuelabels l on l.issueid = jql2.issueid

 

 

 

WHERE

jql.query = 'project = "Upgrade" and issuetype = "Story"'

and  jql2.query = 'project = "Upgrade" and issuetype = "Defect"  and Status not in (Done, Complete, Closed) '

And cf.Name = 'Severity'

   

Nic Brough -Adaptavist-

This is one of the reasons it is a really bad idea to use SQL on a Jira database - it's not a database, it is just storage, and all the releationships and logic is done in the code, not the database.

Your SQL needs to be amended to understand that when an issue has no label, there is nothing to look for.  An issue with 3 labels will have 3 lines in the custom field value table. 2 labels is 2 lines, 1 label 1 line, and no labels means there will be no lines.  Not a null, but nothing there at all.