Subselect der einen Wert zurück gibt

[MScalli]

Hallo,

ich versuche auf unserem Ticketsystem eine Abfrage zu erstellen die folgendes Endergebnis ausgeben soll.
Ticket, Zeit_Gesamt, Techniker

Es gibt folgende Tabellen

glpi_tickes
id	name	closedate
123​	Ticket 123	24.05.2024​
111​	Ticket 111	24.05.2024​
222​	Ticket 222	24.05.2024​
333​	Ticket 333	24.05.2024​

glpi_tickettasks
id	users_id	actiontime
123​	1​	600​
111​	1​	1200​
222​	1​	1200​
123​	2​	600​
333​	2​	1200​
123​	3​	600​

glpi_tickets_users
tickets_id	users_id	type
123​	1​	2​
111​	1​	2​
222​	1​	2​
123​	2​	2​
333​	2​	2​
123​	3​	2​

glpi_users
users_id	realname	firstname
1​	Norris	Chuck
2​	Einstein	Albert
3​	Mouse	Mickey

Folgender SQL Select funktioniert soweit

select distinct t.id,
        (select round(SUM(tt.actiontime) / 60, 0) from glpi_tickettasks tt where tt.tickets_id = t.id) as Zeit_Gesamt from glpi_tickets t
        inner join glpi_tickets_users tu on t.id = tu.tickets_id
WHERE
    t.closedate between '2024-05-13 00:00:00' and '2024-05-20 23:59:59' and   -- Range in der das Ticket geschlossen wurde
    tu.type = 2 and                                                           -- Typ des Tickets                                                                       
    tu.users_id in (1,2,3,4,5,6)                                              -- Alle die mitgewirkt haben
;

und liefert folgendes Ergebnis

ID	Zeit_Gesamt
123​	30​
111​	20​
222​	20​
333​	20​

Jetzt will ich aber alle mitwirkenden durch komma getrennt an jede Zeile hängen, also so soll es aussehen

ID	Zeit_Gesamt	Techniker
123​	30​	Norris, Einstein, Mouse
111​	20​	Norris
222​	20​	Norris
333​	20​	Mouse

Der SQL zum zusammenhängen funktioniert auch soweit

select GROUP_CONCAT(u.realname SEPARATOR ',') as Techniker from glpi_users u where u.id in (1,2,3);

Liefert folgendes Ergebnis

Techniker

Norris, Einstein, Mouse

Aber ich schaffe es leider nicht diese 2 Strings zu verbinden :-(
Hat jemand nen Tipp für mich?

 

[ukulele]

Erstmal würde ich den SELECT besser machen, du machst einen unnötigen Sub-SELECT für jeden Datensatz im SELECT. Dir fehlen die Joins. Das mit dem DISTINCT ist auch nicht schön.

select t.id,
    round(SUM(tt.actiontime) / 60, 0) as Zeit_Gesamt
from glpi_tickets t
inner join glpi_tickets_users tu on t.id = tu.tickets_id
INNER JOIN glpi_tickettasks tt ON t.id = tt.tickets_id AND tu.users_id = tt.users_id
WHERE t.id
    t.closedate between '2024-05-13 00:00:00' and '2024-05-20 23:59:59' and   -- Range in der das Ticket geschlossen wurde
    tu.type = 2 and                                                           -- Typ des Tickets                                                                       
    tu.users_id in (1,2,3,4,5,6)                                              -- Alle die mitgewirkt haben
GROUP BY t.id

Jetzt kann es sein, das dann dein Ergebnis nicht mehr stimmt weil zu einem Datensatz in t mehrere in tu bestehen und ebenfalls mehrere in tt. Dann multipliziert sich sum(tt.actiontime) um die Anzahl der Datensätze in tu. Das habe ich aber versucht zu verhindern in dem ich die Join-Condition enger gefasst habe ( AND tu.users_id = tt.users_id ). Musst du mal prüfen, ob das passt.

Dann kommt dein CONCAT() zum Einsatz, aber ebenfalls nicht als neuer Subselect, das ist kein guter Stil.

select t.id,
    round(SUM(tt.actiontime) / 60, 0) as Zeit_Gesamt,
    GROUP_CONCAT(u.realname SEPARATOR ',') AS Techniker
from glpi_tickets t
inner join glpi_tickets_users tu on t.id = tu.tickets_id
INNER JOIN glpi_tickettasks tt ON t.id = tt.tickets_id AND tu.users_id = tt.users_id
INNER JOIN glpi_users u ON tu.users_id = u.ID
WHERE t.id
    t.closedate between '2024-05-13 00:00:00' and '2024-05-20 23:59:59' and   -- Range in der das Ticket geschlossen wurde
    tu.type = 2 and                                                           -- Typ des Tickets                                                                       
    tu.users_id in (1,2,3,4,5,6)                                              -- Alle die mitgewirkt haben
GROUP BY t.id

Teste das mal, habs selbst nicht probiert

 

[MScalli]

Hi,
Danke für Deine Hilfe und natürlich für den Tipp mit dem Join
Ich habe es zu Beginn auch mit GROUP BY versucht aber bin kläglich gescheitert^^
So wie Du es gepostet hast hat es hingehauen, kleiner Syntax Fehler deswegen hier der fertige code falls jemand auch dieses Problem hat.

SELECT t.id,
    round(SUM(tt.actiontime) / 60, 0) AS Zeit_Gesamt,
    GROUP_CONCAT(u.realname SEPARATOR ',') AS Techniker
FROM glpi_tickets t
INNER JOIN glpi_tickets_users tu ON t.id = tu.tickets_id
INNER JOIN glpi_tickettasks tt ON t.id = tt.tickets_id AND tu.users_id = tt.users_id
INNER JOIN glpi_users u ON tu.users_id = u.ID
WHERE
    t.closedate BETWEEN '2024-05-13 00:00:00' AND '2024-05-20 23:59:59' AND   -- Range in der das Ticket geschlossen wurde
    tu.type = 2 AND                                                           -- Typ des Tickets                                                                       
    tu.users_id IN (1,2,3,4,5)                                                -- Alle die mitgewirkt haben
GROUP BY t.id
;