Abfrageproblematik mit MySQL


Davicito

Erfahrenes Mitglied
#1
Hallo liebes Community,

ich habe eine Frage zu einem Select-Problem.

ich habe eine Tabelle: Mitarbeiter (M_ID, und personalien), eine Tabelle: Kunden und eine Tabelle: Standort
Dazu giebt es die Relationstabelle: Bez_Kunden_Mitarbeiterin dem Mitarbeiter in relation von Kunden und dessen Standort gesetzt werden.

S. Bild - Relationale DB - Kurzübersicht
1548692297272.png
s. Bild - mit Beispieldaten
1548696496330.png

Problemstellung:
Ich möchte mir alle Mitarbeiter anzeigen lassen, die ausgeschieden sind. Sprich nur die Mitarbeiter, wo ein Austrittsdatum, in Tabelle: MAChronik gesetzt ist und falls der Mitarbeiter mehrere Rekords besitzt, muss dazu geprüft werden, das dazu auch kein Rekord mit Austritt NULL oder '000-00-00' definiert ist.

Mit folgendem SQL-Satemant werden mir dazu leider auch alle mit offenem Austritt angezeigt, wie kann man das realisierun, dass zu einer M_ID alle Rekorts geprüft werden (vielleich mit einer Loop) das der Mitarbeiter auch wirklich ausgeschieden ist?

MySQL:
SQL:
SELECT  *
FROM Mitarbeiter m
LEFT JOIN Bez_Kunden_Mitarbeiter bkm ON m.M_ID = bkm.M_ID
LEFT JOIN MAChronik mac ON m.M_ID = mac.M_ID
WHERE  bkm.STGruppen_ID = '1'
     AND mac.Austritt = (SELECT  max(mac2.Austritt)
                                        FROM MAChronik mac2
                                        WHERE  mac2.M_ID = m.M_ID)
     AND (mac.Austritt is not null OR mac.Austritt != "0000-00-00" OR mac.Austritt < "2019-01-28")

GROUP BY bkm.M_ID
ORDER BY m.Name ASC
Ich freu mich über etwas Hilfe bei diesem Thema und Danke es Euch schon im Voraus.
Liebe Grüße!
 
Zuletzt bearbeitet:

Zvoni

Erfahrenes Mitglied
#2
Als erste Idee:
Achtung: Aircode!
SQL:
SELECT A.M_ID, A.Gesamt, B.Gefiltert, B.MAXAustritt FROM
(SELECT M_ID, COUNT(*) AS Gesamt FROM MAChronik GROUP BY M_ID) AS A,
(SELECT M_ID, COUNT(*) AS Gefiltert, MAX(Austritt) AS MAXAustritt  FROM MAChronik WHERE Austritt IS NOT NULL AND Austritt<>'0000-00-00' AND Austritt<'2019-01-28' GROUP BY M_ID) AS B
WHERE
A.M_ID=B.M_ID AND
A.Gesamt=B.Gefiltert
bzw. als INNER JOIN darstellen
EDIT: Habs mit deinen Daten für MAChronik in Access gestestet, und ich erhalte M_ID 8 und 77 geliefert mit dem jeweils höchsten Austrittsdatum
 
Zuletzt bearbeitet:

Zvoni

Erfahrenes Mitglied
#3
Hab gerade noch was bei dir gesehen: Check noch mal deine Primary/Foreign Keys.
Ich sehe da Paarungen, bei welchen einer unsigned hat, der andere nicht
(Beispiel Mitarbeiter.M_ID ist unsigned, MAChronik.M_ID nicht)
 

Davicito

Erfahrenes Mitglied
#5
Als erste Idee:
Achtung: Aircode!
SQL:
SELECT A.M_ID, A.Gesamt, B.Gefiltert, B.MAXAustritt FROM
(SELECT M_ID, COUNT(*) AS Gesamt FROM MAChronik GROUP BY M_ID) AS A,
(SELECT M_ID, COUNT(*) AS Gefiltert, MAX(Austritt) AS MAXAustritt  FROM MAChronik WHERE Austritt IS NOT NULL AND Austritt<>'0000-00-00' AND Austritt<'2019-01-28' GROUP BY M_ID) AS B
WHERE
A.M_ID=B.M_ID AND
A.Gesamt=B.Gefiltert
bzw. als INNER JOIN darstellen
EDIT: Habs mit deinen Daten für MAChronik in Access gestestet, und ich erhalte M_ID 8 und 77 geliefert mit dem jeweils höchsten Austrittsdatum
Hallo Danke für die Hilfestellung.
Ich habe das einmal bei mir ausprobiert und siehe da, er zeigt mir nur weniger an, die auch wirklich ausgeschieden sind, als vorher.
Da habe ich noch eine weitere Frage zu Ihrem Select. Wie kann ich diesen jetzt mit meiner Select-Abfrage kombilieren?
Ich habe ja nun noch zwei Joins über die Tabelle Mitarbeiter und Bez_Kunden_Mitarbeiter, welche auch berücksichtigt werden müssen. Ich benötige als Rückgabe nun auch noch den Vornamen, Nachnamen etc. der/des Mitarbeiter/s.
Muss ich Ihren Select als Sub-Query in dem Join für MAChronik formulieren, oder wie baue ich den am besten? Bei meinen Versuchen bekomme ich immer Fehlermeldungen weil er die Attribute nicht gefunden werden können (z.B. m.M_ID).

Beste Grüße
 

Zvoni

Erfahrenes Mitglied
#6
Hi Davicito,

Wird (was mich betrifft) warten müssen, da ich das nur im Büro machen kann.
Hab jetzt Feierabend (bin auf dem iPad), und morgen und übermorgen habe ich Kundendienst.
Vielleicht kann dir jemand anderes von hier bis dahin weiter helfen.
Ich komme frühestens (wenn überhaupt) morgen Nachmittag dazu, mir das an zu schauen
 

Davicito

Erfahrenes Mitglied
#7
Hi Davicito,

Wird (was mich betrifft) warten müssen, da ich das nur im Büro machen kann.
Hab jetzt Feierabend (bin auf dem iPad), und morgen und übermorgen habe ich Kundendienst.
Vielleicht kann dir jemand anderes von hier bis dahin weiter helfen.
Ich komme frühestens (wenn überhaupt) morgen Nachmittag dazu, mir das an zu schauen
Ah ok. Vielen Dank erstmal. Vielleicht etwas später. Ich schau auch nochmal, ob ich das allein hinbekommen, sonst würde ich mich gern über einen Tipp freuen.

Beste Dank im Voraus! :)
 

Zvoni

Erfahrenes Mitglied
#8
Kurze Frage: kannst du Bespieldaten der betroffenen Tabellen (als CSV oder text in ZIP) hier reinstellen?
Wäre deutlich einfacher für mich (oder jemand anderst)
 

Zvoni

Erfahrenes Mitglied
#9
Ungetestet!

SQL:
SELECT  *
FROM Mitarbeiter AS m
LEFT JOIN
Bez_Kunden_Mitarbeiter AS bkm
ON m.M_ID = bkm.M_ID
LEFT JOIN
(SELECT A.M_ID, A.Gesamt, B.Gefiltert, B.MAXAustritt FROM
(SELECT M_ID, COUNT(*) AS Gesamt FROM MAChronik GROUP BY M_ID) AS A,
(SELECT M_ID, COUNT(*) AS Gefiltert, MAX(Austritt) AS MAXAustritt  FROM MAChronik WHERE Austritt IS NOT NULL AND Austritt<>'0000-00-00' AND Austritt<'2019-01-28' GROUP BY M_ID) AS B
WHERE
A.M_ID=B.M_ID AND
A.Gesamt=B.Gefiltert) AS mac
ON
m.M_ID = mac.M_ID
WHERE  bkm.STGruppen_ID = '1'
GROUP BY bkm.M_ID
ORDER BY m.Name ASC
 

Davicito

Erfahrenes Mitglied
#10
Hallo Zvoni,

super vielen Dank. Es hat zwar lange gedauert, eine Antwort zu formulieren, aber lieber spät als nie. ^^

Der MySQL Code scheint zu funktionieren und gibt mir tatsächlich alle ausgeschiedenen Mitarbeiter zurück.
Nun habe ich aber noch ein paar Verständisfragen zu diesem genialem Code.

Wenn ich jetzt aus der Tabelle "MAChronik" die PersonalNr haben will, habe ich bisher im globalen Select-Query "mac.Personal" zu stehen. Nur bekomme ich dazu eine Fehlermeldung das dieses Attribute nicht bekannt ist. Jedoch steht im letzen Inner-Join ein alias "mac" für die Unerabfragen in der MAChronik-Tabelle.
Muss ich hier im ersten SubQuery - im Select-Bereich - A.PersonalNr / B.PersonalNr schreiben damit er mir die PersonalNr zurückgeben kann? Bei mir hat das nähmlich so nicht funktioniert. Jedoch mit einem zusätzlichen Inner-Join (s. vorletzer Join, der auskommentiert ist), konnte ich mir das Attribute "mac.PersonalNr" zurückgeben.
Und meine zweite Frage, gibt es da vielleicht noch eine bessere Variante zur besseren Performance oder vielleicht der bnesseren Übersichtlichkeit und Verständlichkeit?

Hier mein umgeschriebener/angepasster MySQL-Code
SQL:
SELECT m.M_ID, m.Telefon_1, Telefon_2, m.Email, m.Benutzername, 
            CONCAT_WS(", ", m.Name, m.Vorname) as name, a.Anrede, mac.PersonalNr, 
            br.Group_Color, br.Rolle, mag.Kürzel as gkürzel, mag.Gruppe 
FROM Mitarbeiter m 

INNER JOIN Anrede a on m.A_ID = a.A_ID 
LEFT JOIN Benutzerrolle br on m.BR_ID = br.BR_ID 
INNER JOIN Bez_MAGruppe bmag on m.M_ID = bmag.M_ID 
INNER JOIN MAGruppe mag on bmag.MAG_ID = mag.MAG_ID 
INNER JOIN Bez_Kunden_Mitarbeiter bkm on m.M_ID = bkm.M_ID
#INNER JOIN MAChronik mac on m.M_ID = mac.M_ID

INNER JOIN 
    (SELECT A.M_ID, A.Gesamt, B.Gefiltert, B.MAXAustritt 
       FROM (SELECT M_ID, COUNT(*) AS Gesamt 
                FROM MAChronik GROUP BY M_ID) AS A,
            (SELECT M_ID, COUNT(*) AS Gefiltert, MAX(Austritt) AS MAXAustritt  
                FROM MAChronik 
                WHERE Austritt IS NOT NULL AND Austritt<>'0000-00-00' AND Austritt<'2019-10-14' 
                GROUP BY M_ID) AS B
       WHERE A.M_ID = B.M_ID 
         AND A.Gesamt=B.Gefiltert ) AS mac
ON m.M_ID = mac.M_ID

Where bkm.STGruppen_ID = 1 

GROUP BY bkm.M_ID 
ORDER BY m.Name ASC
 

Zvoni

Erfahrenes Mitglied
#11
Hiho,

Unabhängig davon, dass ich es seltsam finde, eine Personalnr. in der Mitarbeiter Chronik zu finden, anstatt in den Mitarbeiter Stammdaten ;)
Ja, falls du die Personalnr mitliefern willst, reicht es das A-Subselect um dieses Feld zu erweitern.
Nur dran denken, dann auch das Group by um dieses feld zu erweitern

SELECT A.M_ID, A.PersonalNr, A.Gesamt, B.Gefiltert, B.MAXAustritt
FROM (SELECT M_ID, Personalnr, COUNT(*) AS Gesamt
FROM MAChronik GROUP BY M_ID, Personalnr) AS A,

Und schon kannst du das oben mit mac.personalnr ansprechen

Was Performance betrifft, muss ich passen, da ich selten mit MySQL zu tun habe, und auch keinen Query-Analyzer dafür zur Hand habe.
Vielleicht kann dir jeman anderst hier dabei helfen.
 

Davicito

Erfahrenes Mitglied
#12
Hi again,

jo perfekt!!

Hab mal gemessen wie lange es gedauert hat, bis er mir die ausgeschiedenen Mitarbeiter rausgesucht hat.
-> Zeige Datensätze 0 - 499 (694 insgesamt, Die Abfrage dauerte 0.1896 Sekunden.) [M_ID: 1... - 2358...]

Zitat: [Unabhängig davon, dass ich es seltsam finde, eine Personalnr. in der Mitarbeiter Chronik zu finden, anstatt in den Mitarbeiter Stammdaten ;)]

-> Ja das hatten wir auch schon voher so gemacht. Da die PersonalNr aber an einem Mitarbeitervertrag geknüpft ist, und ein Mitarbeiter bei Vertragswechsel (Student Festangestellter Minijobber etc.) eine andere PersonalNr zugewiesen bekommen (daher auch Eintritt/Austritt bei Kündigung und Wiedereinstellung oder Vertragswechsel), musste dies in eine Mitarbeiterchroniktabelle ausgelagert werden, samt Personalnummer. Also kann mann diese Tabelle MAChronik auch als Arbeitsvertragtabelle sehen. -> Auch später für Statistiken:)

Beste Grüße!
 

Zvoni

Erfahrenes Mitglied
#13
In dem Fall dran denken: Da nach M_ID und PersonalNr gruppiert wird, kann es passieren, dass du für gegebene M_ID aber dann mehrere Ergebnisse bekommen kannst, da ja eine M_ID mehrere PersonalNr haben kann.
In deinen Beispieldaten würdest du in der neuen Variante für M_ID=77 3 Ergebnisse bekommen (anstatt wie bisher nur 1), weil für MAC_ID 7 bis 9 jedesmal eine andere PersonalNr existiert.
Falls du wirklich nur die ausgeschiedenen Mitarbeiter sehen willst, würde es vielleicht Sinn machen, die letzte (=Höchste?) PersonalNr zu liefern.

In dem Fall müsstest du das A-SubSelect umbauen in (Auf das Max(PersonalNr) achten sowie der ursprüngliche GROUP BY)
SELECT A.M_ID, A.PersonalNr, A.Gesamt, B.Gefiltert, B.MAXAustritt
FROM (SELECT M_ID, MAX(Personalnr) AS PersonalNr, COUNT(*) AS Gesamt
FROM MAChronik GROUP BY M_ID) AS A,