Query mysql

[Jörg]

Hallo,

ich habe 2 Tabellen, aus denen ich Informationen benötige.

Tabelle 1 enthält eine Kunden-ID und viele weitere Infos. Tabelle 2 enthält zu jeder Kunden-ID mehrere Mitarbeiternamen und weitere Infos.

Nun hätte ich gerne eine Ergebnismenge, die aus Tabelle 1 alle KundenIDs + weitere Infos enthält, die eine bestimmte Bedingung erfüllen, aber jede nur ein mal, egal ob sie die Bedingung mehrfach erfüllen. Und dazu hätte ich gerne zu dieser Kunden-ID alle vorhandenen Namen, Tel., usw.

Das heißt, ich bräuchte dann alle Kunden-IDs + Infos quasi genau so oft, wie es in Tabelle 2 Namen gibt, natürlich mit den entsprechenden Namen, Tel, usw. in den Spalten.

Hoffe, ich habe das nicht zu kompliziert ausgedrückt. Ich weiß grad nicht, wie ich da gruppieren und joinen muss. Kann mir mal jemand auf die Sprünge helfen?

Jörg

[Rolf B]

Hallo Jörg,

ich verstehe das so, dass die KundenID in Tabelle 1 keine ID-Eigenschaft hat, d.h. es kann dort mehrere Rows mit dieser KundenID geben.

Du hast also als ersten Baustein

SELECT kundenid, info1, info2
FROM table1
WHERE /* bedingung */

und bekommst auf diese Weise ein Ergebnis, wo eine KundenID mehrfach vorkommt. Nun frag ich mich: was ist jetzt mit info1, info2, etc? Sind die in dem Fall auch gleich? Wenn nicht, wird es schwierig, denn damit wäre deine Anforderung nicht erfüllbar. Wenn sie aber gleich sind und die KundenID keine ID der Tabelle ist, dann schreit das nach einem Datenmodellfehler, denn dann gehörten info1 und info2 in die Kundentabelle, wo die KundenID eindeutig ist.

Hast Du also einen Datenmodellfehler? Oder verstund ich Dich nur miss?

Wenn es ein Datenmodellfehler ist, kannst Du dieses SELECT-Ergebnis mit DISTINCT auf eindeutige Werte eindampfen. Das ist nicht schön, weil es unnötig Aufwand in der DB macht, aber ich denke mal, eine Modellsanierung nicht nicht das, was Du jetzt vorhast.

SELECT filter1.kundenid, filter1.info1, filter1.info2, table2.info10, table2.info11
FROM (SELECT DISTINCT kundenid, info1, info2
      FROM table1
      WHERE /* bedingung */) filter1
  LEFT JOIN table2 ON filter1.kundenid = table2.kundenid

wäre mein Versuch, basierend auf den gemachten Annahmen.

Rolf

[Jörg]

Hallo Rolf,

ich verstehe das so, dass die KundenID in Tabelle 1 keine ID-Eigenschaft hat, d.h. es kann dort mehrere Rows mit dieser KundenID geben.

Ganz genau so ist es auch.

Du hast also als ersten Baustein

SELECT kundenid, info1, info2
FROM table1
WHERE /* bedingung */

und bekommst auf diese Weise ein Ergebnis, wo eine KundenID mehrfach vorkommt.

Ja, ok.

Nun frag ich mich: was ist jetzt mit info1, info2, etc? Sind die in dem Fall auch gleich?

Nein, das sind sie nur zum Teil.
Die Tabelle 1 beinhaltet sozusagen Aufträge.
Und da sind zum Teil die Adressen (Kunden hat Adresse gewechselt), Bemerkungen oder auf jeden Fall das Auftragsdatum o.ä. natürlich unterschiedlich.

Wenn nicht, wird es schwierig, denn damit wäre deine Anforderung nicht erfüllbar. Wenn sie aber gleich sind und die KundenID keine ID der Tabelle ist, dann schreit das nach einem Datenmodellfehler, denn dann gehörten info1 und info2 in die Kundentabelle, wo die KundenID eindeutig ist.

Zugegeben, man hätte die Tabelle1 noch weiter normalisieren können.
Aber wirklich müssen?
Es stehen alle Infos zu dem jeweiligen Auftrag einfach in dieser Tabelle.
Also KundenID, Kundennummer, PLZ, Ort, usw.
Und ich finde nicht, dass z.b. die Adresse des Kunden (auf den Auftrag bezogen) in die Kundentabelle gehört. Denn die kann sich ändern und in die Kundentabelle gehört nur die aktuelle Adresse, es sei denn, ich führe eine Adresstabelle, die eine Abhängigkeit zu einer Zeittabelle hat. (Ich habe mich hier für die Datenredundanz entschieden, an anderer Stelle habe ich es auch schon anders gemacht, hat irgendwie beides Vor- und Nachteile).

Hast Du also einen Datenmodellfehler? Oder verstund ich Dich nur miss?

Hm, beides nicht.
Ich glaube nihct an einen Datenmodellfehler.
Tabelle 2 enthält einfach Mitarbeiter des Kunden.
Und ich will nun alle Kunden herausfinden, die in einer gewissen Zeitspanne Aufträge generiert haben und möchte der Vollständigkeit halber alle "gelisteten" Ansprechpartner dieses Kunden ebenfalls aufgeführt haben.

Da es aber Kunden mit 0-n Ansprechpartnern gibt, möchte ich dann je Ansprechpartner eine Ergebniszeile generieren.

Wenn es ein Datenmodellfehler ist, kannst Du dieses SELECT-Ergebnis mit DISTINCT auf eindeutige Werte eindampfen. Das ist nicht schön, weil es unnötig Aufwand in der DB macht, aber ich denke mal, eine Modellsanierung nicht nicht das, was Du jetzt vorhast.

SELECT filter1.kundenid, filter1.info1, filter1.info2, table2.info10, table2.info11
FROM (SELECT DISTINCT kundenid, info1, info2
      FROM table1
      WHERE /* bedingung */) filter1
  LEFT JOIN table2 ON filter1.kundenid = table2.kundenid

wäre mein Versuch, basierend auf den gemachten Annahmen.

Kann ich ausprobieren.
Passt die Query noch auf meinen neuen Versuch, das Datenmodel zu erklären?

Gruß, Jörg

[Rolf B]

Hallo Jörg,

Passt die Query noch?

Ich glaube nicht. Ich glaube auch nicht, dass Du mit nur einer Query dein Ziel erreichen kannst. Die Mitarbeiterliste ist von info1 und info2 unabhängig, warum sollen die Mitarbeiter im gleichen Resultset wie info1 und info2 geliefert werden?

Vor allem - was ist, wenn Du aus Tabelle 1 vier mal die KundenID 4711 bekommst und unterschiedliche Werte für info1 und info2?

SELECT kundenid, info1, info2
FROM table1
WHERE /* bedingung */

4711    A    X
4711    A    X
4711    A    Y
4711    B    Y

Info1 könnte die Adresse sein und Info2 die Telefonnummer, d.h. im Verlauf der Zeit hat sich erst die Telefonnummer geändert und dann die Adresse.

Jetzt willst Du die Mitarbeiter beim Kunden 4711 ergänzen. Nehmen wir an, das sind 4 Stück - aber welcher Row willst Du die zuordnen? Ein DISTINCT über kundenid, info1, info2 liefert immer noch 3 Rows, und ein Join hätte damit 12 Zeilen. Ein DISTINCT nur über die Kundenid würde eine Row aus Tabelle 1 liefern und nach dem Join hätten wir dann 4 - aber Du verlierst info1 und info2.

Dazwischen gibt es nichts.

Wenn Du alle Stände von info1 und info2 brauchst, dann musst Du das zuerst und unabhängig von den Mitarbeitern tun. In einer zweiten Query lässt Du info1 und info2 weg, machst den DISTINCT auf die Kundennummer und tust das, was ich im vorigen Post vorschlug.

Wenn Du von info1 und info2 nur den neuesten Stand brauchst, könnte man mit GROUP BY hantieren. Aber auch dann frage ich nach dem Sinn eines JOIN mit den Mitarbeitern - in diesem Abfrageergebnis wären info1 und info2 immer noch redundant drin.

Wie ist das mit der Abfrage auf table1? Liefert die Bedingung immer nur eine Kunden-ID? Oder mehrere? Abhängig davon könnte ein ORDER BY mit LIMIT reichen, oder es wird kompliziert. Welche DB und welche Version davon setzt Du ganz genau ein? Weiß ich nicht mehr.

Rolf

[Jörg]

Hallo Rolf,

vielen Dank für Deine Hilfe und die Denksanstöße.

Jetzt willst Du die Mitarbeiter beim Kunden 4711 ergänzen. Nehmen wir an, das sind 4 Stück - aber welcher Row willst Du die zuordnen? Ein DISTINCT über kundenid, info1, info2 liefert immer noch 3 Rows, und ein Join hätte damit 12 Zeilen. Ein DISTINCT nur über die Kundenid würde eine Row aus Tabelle 1 liefern und nach dem Join hätten wir dann 4 - aber Du verlierst info1 und info2.

Dazwischen gibt es nichts.

Hm, dann ist die Query aber jetzt ohnehin klar.
Denn die unterschiedlichen Informationen bringen mich eh nicht wirklich weiter.
Sollte sich die Adresse geändert haben, muss ich ohnehin die aktuelle nehmen, um den Kunden noch zu erreichen, also ein JOIN über die Stammdatentabelle ist nötig.

SELECT DISTINCT
a.KundenID,
p.Name, 
p.Tel, 
p.Email,
st.Firmenname, 
st.Strasse1, 
st.PLZ, 
st.Ort
FROM Auftraege a
LEFT JOIN Personen p ON a.KundenID = p.KundenID
LEFT JOIN Stammdaten k ON st.KundenID = a.KundenID

Hierzu aber noch 2 Fragen:

Der JOIN auf die Personentabelle muss ein LEFT JOIN sein, weil ich nicht zu jedem Kunden auch Mitarbeiter eingetragen habe.

Aber wie sieht es bei den Stammdaten aus? Hier hätte ich ja auch einen JOIN nehmen können, anstelle des LEFT JOIN. Macht eigentlich keinen Unterschied, oder?

Und die zweite Frage ist etwas komplizierter:

Wenn ich nun in meiner Ergenbnissmenge auch eine Spalte "letzter Auftrag" führen wollte, würde das gehen?
In der Tabelle a ist das Auftragsdatum vorhanden. Aber MAX(a.Auftragsdatum) stutzt mir die Ergebnismenge zusammen.

Welche DB und welche Version davon setzt Du ganz genau ein? Weiß ich nicht mehr.

Oh, müsst ich nachsehen. Irgendeine der aktuellen Maria-DBs ist es.

Jörg

[Rolf B]

Hallo Jörg,

wenn die Existenz garantiert ist, liefern JOIN und LEFT JOIN das gleiche Ergebnis. Der Unterschied kann in den Auswirkungen auf den Query Optimizer bestehen - bei einem JOIN kann er mit einer von beiden Tabellen anfangen, bei einem LEFT JOIN muss er "links" anfangen. Deswegen würde ich LEFT nur hinschreiben, wenn's wirklich nötig ist. Abgesehen davon könnte sich dein 2 Jahre älteres Ich oder sonstwer irgendwann fragen: Warum hab ich hier keinen inneren Join gemacht? KÖNNTE es sein, dass es zu einem Kundensatz keinen Stammsatz gibt? Grübel, denk, zeitverschwend…

Wenn ich nun in meiner Ergenbnismenge auch eine Spalte "letzter Auftrag" führen wollte, würde das gehen?

Klar. Aber wie? Kommt mal wieder drauf an.

Deine aktuell gezeigte Query ist sinnlos, denn die zeigt alle Kunden, die irgendwelche Aufträge haben. Ohne die Auftragsdaten dazu. D.h. es wird sicherlich eine Bedingung geben, die Aufträge filtert. Und nur zu den gefundenen Aufträgen willst Du Kundendaten sehen.

• Warum ist da ein DISTINCT drin? Wenn der nötig ist, kann das auf eine suboptimale Query hinweisen.
• Soll das Query-Ergebnis auch Werte aus der Auftragstabelle enthalten? Doch doch, das geht schon, du kannst 3 Aufträge zeigen und bei jedem das jüngste Datum. Aber muss das?
• Mal angenommen, deine Auftragstabelle hat zum Kunden 12 die Aufträge 42, 97 und 119. Deine Filterbedingung findet die Aufträge 42 und 97, woraufhin Du den Kunden 12 als Queryergebnis bekommst. Aber welches maximale Auftragsdatum willst Du sehen? Vom #97? Oder vom #119?

_Rolf_

-- 
sumpsi - posui - obstruxi

[Jörg]

Hallo Rolf,

Deine aktuell gezeigte Query ist sinnlos, denn die zeigt alle Kunden, die irgendwelche Aufträge haben. Ohne die Auftragsdaten dazu. D.h. es wird sicherlich eine Bedingung geben, die Aufträge filtert. Und nur zu den gefundenen Aufträgen willst Du Kundendaten sehen.

Schon klar.
Die Bedingung habe ich der Einfachheit halber mal weggelassen.

• Warum ist da ein DISTINCT drin? Wenn der nötig ist, kann das auf eine suboptimale Query hinweisen.

Weil ich derzeit aus der Auftragtabelle nur die KundenID ziehe und die will ich genau 1 x haben, egal wieviele Aufträge der bedingung entsprechen.

• Soll das Query-Ergebnis auch Werte aus der Auftragstabelle enthalten?

Muss eigentlich nicht, es ist mehr eine Aufgabe, die ich mir selber zum lernen gerade stelle.

Doch doch, das geht schon, du kannst 3 Aufträge zeigen und bei jedem das jüngste Datum. Aber muss das?

• Mal angenommen, deine Auftragstabelle hat zum Kunden 12 die Aufträge 42, 97 und 119. Deine Filterbedingung findet die Aufträge 42 und 97, woraufhin Du den Kunden 12 als Queryergebnis bekommst. Aber welches maximale Auftragsdatum willst Du sehen? Vom #97? Oder vom #119?

Das jüngste.
Ich will einfach ausgeben, wann der letzte auftragliche Kontakt zum Kunden bestand. Das wird im Übrigen keinen Vorgang betraffen, der nicht ohnehin in der Ergebnissmenge enthalten its, wiel die Bediungung sich auf einen Zeitraum beziehen wird, innerhalkb derer die Aufträge sind. (z.b. nur letztes Jahr)

Jörg

[Rolf B]

Hallo Jörg,

Das jüngste.
Ich will einfach ausgeben, wann der letzte auftragliche Kontakt zum Kunden bestand.

Och menno - ich frag nach der Nummer und Du wiederholst deine Mehrdeutigkeit, statt eine Nummer zu nennen und sie damit klarzustellen.

Das wird im Übrigen keinen Vorgang betreffen, der nicht ohnehin in der Ergebnismenge enthalten ist,

An dieser Stelle regt sich mein Zweifel, denn:

Weil die Bedingung sich auf einen Zeitraum beziehen wird, innerhalb derer die Aufträge sind. (z.b. nur letztes Jahr)

Und hier sehe ich die Mehrdeutigkeit deiner Formulierung: Wenn es einen Auftrag aus 2022 gibt und die Bedingung zwei Aufträge aus 2021 selektiert, ist der von Dir gewünschte „jüngste Auftrag“ dann aus 2021 oder 2022? Oder geht jede Selektion immer bis zur Gegenwart?

Rolf

[Jörg]

Hallo Rolf,

Das jüngste.
Ich will einfach ausgeben, wann der letzte auftragliche Kontakt zum Kunden bestand.

Och menno - ich frag nach der Nummer und Du wiederholst deine Mehrdeutigkeit, statt eine Nummer zu nennen und sie damit klarzustellen.

Na aber wenn Du gefragt hättest, welches meiner Kinder Fußball spielt und ich Dir gesagt hätte, das Jüngste, bräuchtest Du dann noch den Namen des Kindes, wenn Du mir 3 hypothetische vorgegeben hättest? 😉
Nochmal zur Verdeutlichung: Ich würde dann gerne das Datum des Auftrages haben, welches am nähesten zum Datum der Anwendung der Query liegt. Und dieser Auftrag wird immmer auch in der Ergebnismenge sein.

Weil die Bedingung sich auf einen Zeitraum beziehen wird, innerhalb derer die Aufträge sind. (z.b. nur letztes Jahr)

Und hier sehe ich die Mehrdeutigkeit deiner Formulierung: Wenn es einen Auftrag aus 2022 gibt und die Bedingung zwei Aufträge aus 2021 selektiert, ist der von Dir gewünschte „jüngste Auftrag“ dann aus 2021 oder 2022? Oder geht jede Selektion immer bis zur Gegenwart?

Absolut.
Jedwede Selektion endet mit der Bedingung, die zeitlicher Natur ist und mit dem jeweiligen Tag der Anwendung der Query endet.

Sorry, wenn ich mich missverständlich ausgedrückt hatte.

Jörg

[Rolf B]

Hallo Jörg,

Na aber wenn Du gefragt hättest, welches meiner Kinder Fußball spielt und ich Dir gesagt hätte, das Jüngste, bräuchtest Du dann noch den Namen des Kindes, wenn Du mir 3 hypothetische vorgegeben hättest?

Wenn ich 2 deiner Kinder sehe und dich frage, welches deiner Kinder Fußball spielt, und du antwortest: "das jüngste", dann weiß ich immer noch nicht, ob das jüngste bei den beiden dabei ist. Sprich: Ob ich den Fußballer sehe.

Der Knackpunkt ist: geht es um "deine Kinder" - also alle 3 - oder "diese Kinder" - also nur die beiden, die ich sehe.

Aber ok

Jedwede Selektion endet mit der Bedingung, die zeitlicher Natur ist und mit dem jeweiligen Tag der Anwendung der Query endet.

D.h. am heutigen Tag ist eine Selektion von Aufträgen aus 2021, ohne die 2022er, nicht möglich.

Dann würde ich Dir vorschlagen:

SELECT a.KundenID,
       p.Name, p.Tel, p.Email,
       st.Firmenname, st.Strasse1, st.PLZ, st.Ort,
       a.letzter_auftrag
FROM
     (SELECT a1.kundenid, max(a1.auftragdatum) as letzter_auftrag
      FROM auftraege a1
      WHERE /* bedingung für Aufträge */
      GROUP BY a1.kundenid) a
JOIN
     kunden     k  ON k.kundenid=a.kundenid
JOIN
     stammdaten st ON st.kundenid=a.kundenid

Der innere SELECT liefert die nötigen Kunden-IDs sowie die zugehörige jüngste Auftragsnummer im selektierten Zeitraum. Der GROUP BY sorgt dafür, dass an dieser Stelle eine KundenID nur einmal entsteht.

Kunden- und Stammdaten werden danach erst dazugeJOINt.

WÄREN Abfragen "2020-2021" möglich gewesen und du hättest als jüngsten Auftrag den aus 2022 sehen wollen, hätte man den separat in einem Subselect ermitteln müssen.

Rolf

[Jörg]

Hallo Rolf,

Wenn ich 2 deiner Kinder sehe und dich frage, welches deiner Kinder Fußball spielt, und du antwortest: "das jüngste", dann weiß ich immer noch nicht, ob das jüngste bei den beiden dabei ist. Sprich: Ob ich den Fußballer sehe.

Der Knackpunkt ist: geht es um "deine Kinder" - also alle 3 - oder "diese Kinder" - also nur die beiden, die ich sehe.

Nach meiner Prämisse (also der WHERE-Bedingung) bekommst Du ausschließlich alle meine Kinder zu Gesicht. 😉

Aber ok

Jedwede Selektion endet mit der Bedingung, die zeitlicher Natur ist und mit dem jeweiligen Tag der Anwendung der Query endet.

D.h. am heutigen Tag ist eine Selektion von Aufträgen aus 2021, ohne die 2022er, nicht möglich.

Ganz genau so ist es.

Dann würde ich Dir vorschlagen:

SELECT a.KundenID,
       p.Name, p.Tel, p.Email,
       st.Firmenname, st.Strasse1, st.PLZ, st.Ort,
       a.letzter_auftrag
FROM
     (SELECT a1.kundenid, max(a1.auftragdatum) as letzter_auftrag
      FROM auftraege a1
      WHERE /* bedingung für Aufträge */
      GROUP BY a1.kundenid) a
JOIN
     kunden     k  ON k.kundenid=a.kundenid
JOIN
     stammdaten st ON st.kundenid=a.kundenid

Der innere SELECT liefert die nötigen Kunden-IDs sowie die zugehörige jüngste Auftragsnummer im selektierten Zeitraum. Der GROUP BY sorgt dafür, dass an dieser Stelle eine KundenID nur einmal entsteht.

Kunden- und Stammdaten werden danach erst dazugeJOINt.

Oh, welch wunderschöne Query.
Ja, zugegeben, darauf wäre ich nicht gekommen. 👍
Muss aber anmerken, dass sie natürlich niemals laufen würde, weil Du anstelle des LEFT JOINS auf die Personentabelle die Kundentabelle 2 x dazu innerjoinst. Aber das ist egal, ich weiß, was Du meinst und kann das für mich dann schon so umsetzen, dass sie laufen wird. Leider sitze ich grad am falschen Rechner, aber ich geb Dir später oder morgen Rückmeldung, ob sie auch das ausspuckt, was ich gerne hätte. Aber ein von der Logik her sollte sie passen.

WÄREN Abfragen "2020-2021" möglich gewesen und du hättest als jüngsten Auftrag den aus 2022 sehen wollen, hätte man den separat in einem Subselect ermitteln müssen.

Ist in meinem Fall nicht nötig, weil, wie gesagt immer bis zum Tag der Query gefragt wird.

Vielen Dank für Deine Hilfe.👍

Jörg

[Jörg]

weil Du anstelle des LEFT JOINS auf die Personentabelle die Kundentabelle 2 x dazu innerjoinst.

Sollte natürlich heißen, 1 x Kundentabelle, 1 x Stammdatentabelle dazu innerjoinst. 😉

[Jörg]

also:

SELECT a.KundenID,
       p.Name, p.Tel, p.Email,
       st.Firmenname, st.Strasse1, st.PLZ, st.Ort,
       a.letzter_auftrag
FROM
     (SELECT a1.kundenid, max(a1.auftragdatum) as letzter_auftrag
      FROM auftraege a1
      WHERE /* bedingung für Aufträge */
      GROUP BY a1.kundenid) a
LEFT JOIN
     Personen p  ON p.kundenid=a.kundenid
JOIN
     stammdaten st ON st.kundenid=a.kundenid