MySQL Subquery for Custom field return more than one row

[s.mallmann]

Hello at all,

I have a SQL Statement for generating a report that gives me all Incidents with some more Informations. For a few custom field i have some subquerys that work fine if there run alone. If there run as sub query at the complete statement they return more than one row.

The Statement: Sub Q's are underlined. One Query for each Customfield

SELECT
ji.issuenum AS "Incident ID",
issuestatus.pname AS "Incident Status",
ji.priority AS Priorität,
-- ji.component AS "Kategorie betroffenes Produkt",
-- ji.component AS "Kategorie verursachendes Produkt",
    (SELECT cfo.customvalue FROM customfieldvalue cfv, customfieldoption cfo, jiraissue ji, issuetype it where cfv.customfield = 10139 and
     cfv.customfield = cfo.customfield and
      CAST(cfv.stringvalue as SIGNED) = cfo.id and
       cfv.issue = ji.id and
         ji.issuetype= "10002") AS "Umgebung",
    (SELECT cfo.customvalue FROM customfieldvalue cfv, customfieldoption cfo, jiraissue ji, issuetype it where cfv.customfield = 10133 and
     cfv.customfield = cfo.customfield and
      CAST(cfv.stringvalue as SIGNED) = cfo.id and
       cfv.issue = ji.id and
         ji.issuetype= "10002") AS "Sicherheitsrelevant",
    (SELECT cfo.customvalue FROM customfieldvalue cfv, customfieldoption cfo, jiraissue ji, issuetype it where cfv.customfield = 10135 and
     cfv.customfield = cfo.customfield and
      CAST(cfv.stringvalue as SIGNED) = cfo.id and
       cfv.issue = ji.id and
         ji.issuetype= "10002") AS "Datenschutzrelevant",
    (SELECT cfo.customvalue FROM customfieldvalue cfv, customfieldoption cfo, jiraissue ji, issuetype it where cfv.customfield = 10132 and
     cfv.customfield = cfo.customfield and
      CAST(cfv.stringvalue as SIGNED) = cfo.id and
       cfv.issue = ji.id and
         ji.issuetype= "10002") AS "TI-NOTFALL",
DATE_FORMAT(ji.created, '%Y-%m-%dT%T%+02' )  AS "Zieltermin",
ji.reporter AS "Incident Melder",
ji.assignee AS "Incident Bearbeiter",
ji.summary AS "Incident Beschreibung",
-- DATE_FORMAT(ji.duedate, '%Y-%m-%dT%T%+02' )  AS "Zieltermin",
resolution.pname  AS "Indicent Lösung"
FROM jiraissue ji join project on ji.project=project.ID
join issuetype on ji.issuetype=issuetype.ID
join issuestatus on ji.issuestatus=issuestatus.ID
left outer join resolution on ji.resolution=resolution.ID
where ji.issuetype = "10002";

As I said. If I run this:

SELECT cfo.customvalue FROM customfieldvalue cfv, customfieldoption cfo, jiraissue ji, issuetype it where cfv.customfield = 10139 and
     cfv.customfield = cfo.customfield and
      CAST(cfv.stringvalue as SIGNED) = cfo.id and
       cfv.issue = ji.id and
         ji.issuetype= "10002"

 I get the correct table of results.

Can anybody help me?

Best regards,

Stephan

[s.mallmann · Accepted solution]

SELECT
ji.issuenum AS "Incident ID",
issuestatus.pname AS "Incident Status",
ji.priority AS Priorität,
Umgebung.Umgebung,
Sicherheitsrelevant.Sicherheitsrelevant,
Datenschutzrelevant.Datenschutzrelevant,
TI_NOTFALL.TI_NOTFALL as "TI-NOTFALL",
DATE_FORMAT(ji.created, '%Y-%m-%dT%T%+02' )  AS "Zieltermin",
ji.reporter AS "Incident Melder",
ji.assignee AS "Incident Bearbeiter",
ji.summary AS "Incident Beschreibung",
resolution.pname  AS "Indicent Lösung"
FROM jiraissue ji join project on ji.project=project.ID
join issuetype on ji.issuetype=issuetype.ID
join issuestatus on ji.issuestatus=issuestatus.ID
left outer join resolution on ji.resolution=resolution.ID
left outer join(
SELECT  ji.id,cfo.customvalue Umgebung FROM customfieldvalue cfv, customfieldoption cfo, jiraissue ji, issuetype it
where cfv.customfield = 10139 and
cfv.customfield = cfo.customfield and
cfv.stringvalue = cfo.id and
cfv.issue = ji.id and
ji.issuetype = "10002" and
ji.issuetype =it.id
) Umgebung on ji.id=Umgebung.ID
left outer join(
SELECT ji.id,cfo.customvalue Sicherheitsrelevant FROM customfieldvalue cfv, customfieldoption cfo, jiraissue ji, issuetype it
where cfv.customfield = 10133  and
cfv.customfield = cfo.customfield and
cfv.stringvalue = cfo.id and
cfv.issue = ji.id and
ji.issuetype = "10002" and
ji.issuetype =it.id
) Sicherheitsrelevant on ji.id=Sicherheitsrelevant.ID
left outer join(
Select ji.id, cfo.customvalue Datenschutzrelevant FROM customfieldvalue cfv, customfieldoption cfo, jiraissue ji, issuetype it
where cfv.customfield = 10135 and
cfv.customfield = cfo.customfield and
cfv.stringvalue = cfo.id and
cfv.issue = ji.id and
ji.issuetype = "10002" and
ji.issuetype =it.id
) Datenschutzrelevant on ji.id=Datenschutzrelevant.ID
left outer join(
Select ji.id, cfo.customvalue TI_NOTFALL FROM customfieldvalue cfv, customfieldoption cfo, jiraissue ji, issuetype it
where cfv.customfield = 10132 and
cfv.customfield = cfo.customfield and
cfv.stringvalue = cfo.id and
cfv.issue = ji.id and
ji.issuetype = "10002" and
ji.issuetype =it.id
) TI_NOTFALL on ji.id=TI_NOTFALL.ID
where ji.issuetype = "10002";

That fix it for me

[Nic Brough -Adaptavist-]

It looks right to me, for cases where you've got multiple entries in the fields.

I suspect it's more a case that the Jira database is absolutely not designed for reporting and you should not be doing your reports like this because you don't fully understand how it hangs together.