mysql - 2 Queries oder doch eine?

[Jörg]

Guten Tag,

ich fange mal direkt mit der Query an und versuche meine Frage daran entlang zu hangeln.

SELECT
    m.ID,
    usw.
    FROM
        table1 m
    LEFT JOIN table2 dg ON m.ID = dg.ID
    LEFT JOIN table3 s ON s.ID = m.ID
    LEFT JOIN table4 mn ON mn.MID = s.MID
    WHERE
        m.ID IN(1094,1095) AND 
        m.Del != 1 AND 
        dg.del != 1 AND 
        mn.MID = 4780

Eigentlich benötige ich alle IDs + "Zubehör", die ich bereits habe (1094,1095). Ich hätte gerne lediglich zusätzlich aus den anderen Tabellen weitere Infos, sofern die restlichen 3 WHERE-Klauseln (alle 3) ebenfalls erfüllt sind.

Die Grundangaben aus Tabelle1 benötige ich zu 1094 und 1095 aber auf jeden Fall.

Dieses mit 2 Queries zu ermitteln, ist nicht meine Frage, das bekomme ich schon hin. Ich versuche gerade, das in einer einzigen Query sinnvoll zu ermitteln.

Geht das und wenn ja, wie (UNION mal außen vor gelassen)?

Gruß, Jörg

[Rolf B]

Hallo Jörg,

d.h. wenn m.Del den Wert 1 hat, kannst Du auf alle drei LEFT JOIN Ergebnisse verzichten, brauchst aber die Werte aus der m-Tabelle trotzdem?

WHERE wirkt auf das Ergebnis des JOIN, und ON wirkt auf die zugeJOINte Table. Probier's mal so:

SELECT
    m.ID,
    usw.
    FROM
        table1 m
    LEFT JOIN table2 dg ON m.Del != 1 AND dg.ID  = m.ID  AND dg.del != 1 
    LEFT JOIN table3 s  ON m.Del != 1 AND  s.ID  = m.ID 
    LEFT JOIN table4 mn ON m.Del != 1 AND mn.MID = s.MID AND mn.MID = 4780
    WHERE
        m.ID IN(1094,1095)

Man müsste Explains laufen lassen, um zu prüfen, ob die Reihenfolge der Abfragen im ON eine Relevanz für den Query-Plan hat oder ob die DB das sinnvoll optimiert. Zumindest im dritten LEFT JOIN wäre es nämlich sinnvoll, bei s.MID != 4780 gar nicht erst die Tabelle anzugucken, und wenn der Server strikt von links nach rechts abfragt, wäre
ON m.Del != 1 AND s.MID = 4780 AND mn.MID = s.MID
wohl performanter. Das kann man aber vermutlich nur mit einer großen DB und einer wilden Abfrage auf zufällige Daten wirklich feststellen.

Rolf

[Jörg]

Hallo Rolf,

d.h. wenn m.Del den Wert 1 hat, kannst Du auf alle drei LEFT JOIN Ergebnisse verzichten, brauchst aber die Werte aus der m-Tabelle trotzdem?

Nein, dann würde ich sowohl die m-Werte als auch die restlichen benötigen. Lediglich wenn es in der dg-Tabelle zur m.ID dann keinen Eintrag gibt, könnte ich in diesem Fall auf sämtliche Werte (t1-t4) verzichten.

Gruß, Jörg

[Rolf B]

Hallo Jörg,

lies nochmal was ich schrieb oder erkläre mir meinen Denkfehler. Aus meiner Sicht passt dies

wenn m.Del den Wert 1 hat

... dann würde ich sowohl die m-Werte als auch die restlichen benötigen.

und das

    WHERE ... AND m.Del != 1 AND ...

nicht zusammen.

Rolf

[Jörg]

Hallo Rolf,

lies nochmal was ich schrieb oder erkläre mir meinen Denkfehler. Aus meiner Sicht passt dies

Nein, Du hast keinen Denkfehler. Es beschreibt meine Frage, die ich aber schwer in Worte fassen kann.

ich versuchs aber noch mal:

Ich habe Tabelle 1, aus der ich Grundwerte eines Artikels benötige. Für jeden dieser Artikel kann in Tabelle 4 ein Vorgang erzeugt werden. Da aber auch mehrere Artikel in einen Vorgang übernommen werden können, werden die Artikel über Tabelle 3 dem Vorgang zugeordnet. Nun können in Tabelle 2 je Artikel aber auch ganz andere Vorgänge erzeugt werden.

An dieser Stelle stehe ich nun. Der User ruft ein Interface (für den Vorgang / Tabelle 2) auf und soll alle dem Vorgang zugeordnete Artikel angezeigt bekommen und darüber hinaus auch die Tabelle2-Vorgänge, die bereits angelegt sind.

Sollte ein Artikel inzwischen gelöscht sein (m.del=1), dafür aber bereits ein Tabelle2-Vorgang existent, soll dieser trortdem ganz normal angezeit werden. Existiert kein Tabelle2-Vorgang, dann wirde auch der rest des "m.del=1-Artikels" nicht angezeigt.

Ich hoffe, ich habe in der Geschichte keine Unlogik eingebaut, da sie nicht ganz der Realität entspricht, aber im Großen und Ganzen sollte es ganz gut beschrieben, was ich meine. Falls nicht, bitte gerne nachfragen.

Gruß, Jörg

[Rolf B]

Hallo Jörg,

Ich hoffe, ich habe in der Geschichte keine Unlogik eingebaut

Ich verstehe sie nicht.

Aus deinen Beschreibungen lese ich dieses Datenmodell heraus (erstellt mit dbdiagram.io):

Ist das so richtig? Insbesondere die Kardinalitäten? Du beschreibst zwischen Table1 und Table4 eine m:n Beziehung und verwendest sie auch in der Query. Table1 scheint aber zu Table2 in einer 1:n Beziehung zu stehen.

Insbesondere komme ich nicht damit klar, warum table2 und table4 Vorgänge enthalten und warum table4 überhaupt in der Query ist, wenn doch der User Vorgänge aus Table2 auswählt und die weiteren Vorgänge auch nur aus Table2 kommen sollen.

Und dann das hier:

Sollte ein Artikel inzwischen gelöscht sein (m.del=1), dafür aber bereits ein Tabelle2-Vorgang existent, soll dieser trortdem ganz normal angezeit werden.

Error: duplicate reference at rolfb.cmd:34677642^[1]

Ich will damit fragen, ob das fett gesetzte dieser der Artikel oder der Vorgang ist.

Aber wie auch immer - ich würde bezweifeln, dass es sinnvoll ist, all das in eine JOIN Query zu packen. Durch die Joins entstehen jede Menge Kombinationen zwischen Artikeln und Vorgängen, so dass Du nachher - mutmaßlich - jede Menge redundante Daten im Ergebnis hast die Du dann im Programmcode wieder deduplizieren musst. Ein UNION ist auch so eine Sache, du kannst nicht Artikel und Vorgänge mit UNION zusammenpacken, weil ein Union strukturelle Gleichheit vorausetzt.

Wenn Du vom User eine Vorgangs-ID als Einstieg bekommts, wären zwei bis drei Queries vermutlich besser. Die erste bestimmt die Artikel zum Vorgang, und die zweite alle Vorgänge, die mit diesen Artikeln in Beziehung stehen. Ob der zweite Schritt in einer Query oder in zweien gemacht werden muss, hängt davon ab, ob table2 und table4 strukturgleich sind. Wenn ja, kann man einen UNION machen. Wenn nein, kann man es vielleicht geschickt zusammenfügen. Vielleicht.

Rolf

---

1. Abgeleitet von: Für die einen ist es Windows, für die anderen die größte und langsamste Batchdatei der Welt 🤣 ↩︎

[Jörg]

Hallo Rolf,

Ich hoffe, ich habe in der Geschichte keine Unlogik eingebaut

Ich verstehe sie nicht.

Liegt an mir, resp. meiner Erklärung 😉

Aus deinen Beschreibungen lese ich dieses Datenmodell heraus (erstellt mit dbdiagram.io):

Haut hin.

Ist das so richtig? Insbesondere die Kardinalitäten? Du beschreibst zwischen Table1 und Table4 eine m:n Beziehung und verwendest sie auch in der Query. Table1 scheint aber zu Table2 in einer 1:n Beziehung zu stehen.

Stell Dir einfach vor, dass der User eine Art Warenkorb (Tabelle 4) mit Artikeln (Tabelle 1) füllt (über Tabelle 3 zusammengefasst). Nun kann er zudem zu jedem Artikel ein (wie und was auch immer geartetes) Protokol (Tabelle 2) anlegen.

Insbesondere komme ich nicht damit klar, warum table2 und table4 Vorgänge enthalten und warum table4 überhaupt in der Query ist, wenn doch der User Vorgänge aus Table2 auswählt und die weiteren Vorgänge auch nur aus Table2 kommen sollen.

Nein, der User befindet sich im Warenkorb und will einen Protokollvorgang zu einem (oder mehreren) Artikel(n) erstellen.

Und genau hier möchte ich ihm alle Artikel auflisten, die der Warenkorb enthält, aber auch alle bereits angelegten Protokolle.

Und dann das hier:

Sollte ein Artikel inzwischen gelöscht sein (m.del=1), dafür aber bereits ein Tabelle2-Vorgang existent, soll dieser trortdem ganz normal angezeit werden.

Damit ist gemeint, dass wenn der Artikel aus dem Programm gestrichen ist, will ich zuvor angelegte Warenkörbe inkl. diesem Artikel und der Protokolle für diesen Artikel behalten und auch anzeigen.

Wenn Du vom User eine Vorgangs-ID als Einstieg bekommts, wären zwei bis drei Queries vermutlich besser. Die erste bestimmt die Artikel zum Vorgang, und die zweite alle Vorgänge, die mit diesen Artikeln in Beziehung stehen.

Ich fürchte auch, dass ich es splitten muss. Schade, schön wärs gewesen, wenn das elegant über eine einzige Query hätte erledigt werden können.

Danke fürs Mitdenken auf jeden Fall,

Jörg

[Rolf B]

Hallo Jörg,

d.h. Warenkörbe und Artikelbewertungen Protokolle sind strukturell sehr verschieden.

Klingt nach 3 Queries.

Rolf

[Jörg]

Hallo Rolf,

d.h. Warenkörbe und Artikelbewertungen

schöne Analogie, hätt' ich selber drauf kommen können/sollen :D

Aber wiese 3 Queries?

1. Artikel im Warenkorb auflisten
2. Bewertung je Artikel, falls vorhanden
3. ???

Jörg

[Rolf B]

Hallo Jörg,

ich dachte an Vorgänge. Aber da bin ich dann wohl mit deiner Doppelbelegung des Begriffs für table2 und table4 durcheinander gekommen.

Den Warenkorb selbst wirst Du aber auch brauchen, oder?

Also

-- Allg. Daten zum Warenkorb einlesen
SELECT w.mid, w.foo, w.bar, w.baz 
FROM   table4 w 
WHERE  w.mid = 4780

-- Artikel im Warenkorb holen
SELECT s.anzahl, s.dings, s.bums, a.id, a.name, a.del, foo, bar, baz 
FROM   table3 s JOIN artikel a ON s.id = a.id 
WHERE  s.mid = 4780

An dieser Stelle solltest Du die Artikelliste übernehmen und die Artikel-IDs zu einer kommaseparierten Liste machen

SELECT dg.id, dg.bla, dg.blub, dg.hui
FROM   table2 dg
WHERE  dg.id IN ($artikelliste)

$artikelliste in den SQL String zu setzen wäre PHP - ich weiß nicht welche Sprache Du verwendest. Maskieren ist hier nicht erforderlich, wenn die aus der Artikeltabelle gelesenen IDs numerisch sind. An welchen Stellen Du noch auf das del Kennzeichen abfragen musst, ist mir nicht so klar.

Eine IN Klausel hat den Nachteil, dass man die Werte darin nicht per Prepare einsteuern kann. Also jedenfalls nicht als Array, man müsste für jeden Wert einen eigenen Parametermarker verwenden und das ist Quatsch, wenn die Anzahl variabel ist. Die Alternative zum dynamisch aufgebauten SQL String wäre eine Subquery, die die Artikel-IDs liefert. Dafür reicht table3. Das sollte fix genug sein, denn die Daten sollten dann noch im Cache liegen. Ob es für Dich schnell genug ist, musst Du testen. Die reine SQL Performance ist nicht allein entscheidend, denn die Host-Programmiersprache muss ja im anderen Fall auch den Id-String aufbauen und das SQL Statement muss neu geparsed werden.

SELECT dg.id, dg.bla, dg.blub, dg.hui
FROM   table2 dg
WHERE  dg.id IN (SELECT s.id
                 FROM   table3 s
                 WHERE s.mid = 4780)

Rolf

[Jörg]

Hallo Rolf,

Den Warenkorb selbst wirst Du aber auch brauchen, oder?

Jain, denn dort ist der Ausgangspunkt. Er ist insofern vorhanden. Auch $artikelliste ist bereits vorhanden, weil als Funktion hinterlegt und bereits an dieser Stelle im Programm genutzt.

Also

-- Allg. Daten zum Warenkorb einlesen
SELECT w.mid, w.foo, w.bar, w.baz 
FROM   table4 w 
WHERE  w.mid = 4780

-- Artikel im Warenkorb holen
SELECT s.anzahl, s.dings, s.bums, a.id, a.name, a.del, foo, bar, baz 
FROM   table3 s JOIN artikel a ON s.id = a.id 
WHERE  s.mid = 4780

An dieser Stelle solltest Du die Artikelliste übernehmen und die Artikel-IDs zu einer kommaseparierten Liste machen

SELECT dg.id, dg.bla, dg.blub, dg.hui
FROM   table2 dg
WHERE  dg.id IN ($artikelliste)

Nicht bös sein, aber ich glaube, so wird das eher nichts. Du hast die Daten aus Tabelle1 unberücksichtigt, die sind aber wichtig (oder meintest Du das mit Deiner Eingangsfrage nach dem Warenkorb?).

Meine Idee:

Query 1: (ermittelt die Artikel)
SELECT m.t1_id, m.bla, m.blub, m.hui
FROM   table1 m
JOIN   table3 t3 ON m.t1_id = t3.t1_id 
WHERE  t3.mid = 4780
AND m.del != 1


Query 2: (ermittelt die Artikelbewertungen)
SELECT dg.t2_id, dg.bla, dg.blub, dg.hui
FROM      table 1 m
LEFT JOIN table2 dg ON m.t1_id = dg.t1_id
WHERE  dg.t1_id IN ($artikelliste) 
AND dg.del != 1

Was mir noch fehlt, ist der Zusammenhang aus Query1+2, also die Zuordnung der Bewertungen zum Artikel.

Jörg

[Rolf B]

Hallo Jörg,

Nicht bös sein, aber ich glaube, so wird das eher nichts. Du hast die Daten aus Tabelle1 unberücksichtigt,

Nee, hab ich berücksichtigt, nur habe ich die Tabelle unachtsamerweise artikel statt table1 genannt. Steht doch drüber: „Artikel im Warenkorb holen“.

Deine Query1 tut das, was meine Query auch tut, ich hab nur noch ein paar Daten aus der table3 dazugepinnt (was einem Warenkorb entspricht: 7 Unterhosen, 9 Socken, etc) - aber da deine Daten mutmaßlich ganz anders aussehen, ist das reiner Spekulatius zu Ostern.

Der Unterschied ist nur, dass Du m.del in der Where Bedingung hast, d.h. du liest gelöschte Artikel nicht mit. Aber sagtest Du nicht, dass Du die trotz Löschung mit lesen wolltest?

Beim Lesen der Artikelbewertungen brauchst Du table1 nicht, wenn Du die benötigten IDs in der Artikelliste hast. Du machst einen JOIN von table1 und table2, verwendest aber keine Daten aus table1. Weder im SELECT noch im WHERE. Wozu also Joinen? Allerdings hast Du da ein bisschen an den Spaltennamen rumgemacht, das war vorher anders. Du hast jetzt t1_id und t2_id - ist das die Artikel-ID und die Bewertungs-ID? Dann solltest Du dg.t1_id mit selektieren.

Wenn Du das gemacht hast, kannst Du

• das Array mit Artikeln im PHP über die Artikel-ID schlüsseln
• für jede gefundene Bewertung den Artikel-Eintrag rausholen und die Bewertung dort an eine Bewertungen-Array anpappen.

$warenkorbinhalt = ARRAY();

/* SQL Query 1 ausführen */

while ($artikel = /* row holen, z.B. mysqli_fetch_assoc */)
{
   $artikel['bewertungen'] = ARRAY();    // Array für Bewertungen vorbereiten
   $warenkorbinhalt[$artikel['t1_id']] = $artikel;
}

/* $artikelliste aufbauen */
/* SQL Query 2 ausführen */

while ($bewertung = /* row holen */)
{
   $artikel = $warenkorbinhalt[$bewertung['t1_id']];
   $artikel['bewertungen'][] = $bewertung;
}

Fertig. Und das gibst Du dann so, wie Du es brauchst, als HTML aus.

Rolf

[Jörg]

Hallo Rolf,

Nee, hab ich berücksichtigt, nur habe ich die Tabelle unachtsamerweise artikel statt table1 genannt. Steht doch drüber: „Artikel im Warenkorb holen“.

Stimmt. Nehme alles zurück und behaupte fortan das Gegentum 😉

Deine Query1 tut das, was meine Query auch tut, ich hab nur noch ein paar Daten aus der table3 dazugepinnt (was einem Warenkorb entspricht: 7 Unterhosen, 9 Socken, etc) - aber da deine Daten mutmaßlich ganz anders aussehen, ist das reiner Spekulatius zu Ostern.

Ja. T3 ist eine reine Zuordnungstabelle (t1_id, t5_id).

Der Unterschied ist nur, dass Du m.del in der Where Bedingung hast, d.h. du liest gelöschte Artikel nicht mit. Aber sagtest Du nicht, dass Du die trotz Löschung mit lesen wolltest?

In der ersten Query muss es mit hinein, weil ich ansonsten Artikel aufliste, die ausgelistet sind. Es sollen nur die Bewertungen angezeigt werden, nicht die Artikel selber. Hört sich erstmal doof an, ist aber schlußendlich logisch, wenn man die (viel zu kompliziert zu erklärende RL-Lösung kennt)

Beim Lesen der Artikelbewertungen brauchst Du table1 nicht, wenn Du die benötigten IDs in der Artikelliste hast. Du machst einen JOIN von table1 und table2, verwendest aber keine Daten aus table1. Weder im SELECT noch im WHERE. Wozu also Joinen?

Sorry, ich muss hier joinen, weil die Query verkürzt ist, ich aber "in Echt" doch auch noch Daten aus T1 brauche.

Allerdings hast Du da ein bisschen an den Spaltennamen rumgemacht, das war vorher anders. Du hast jetzt t1_id und t2_id - ist das die Artikel-ID und die Bewertungs-ID? Dann solltest Du dg.t1_id mit selektieren.

Ich habe das extra gemacht, damit die Zuordnungen der Tabellen zueinander und die vorhandenen Spalten in den Tabellen klarer werden.

Wenn Du das gemacht hast, kannst Du

• das Array mit Artikeln im PHP über die Artikel-ID schlüsseln
• für jede gefundene Bewertung den Artikel-Eintrag rausholen und die Bewertung dort an eine Bewertungen-Array anpappen....

Fertig. Und das gibst Du dann so, wie Du es brauchst, als HTML aus.

Stimmt, das muss dann php übernehmen.

Danke für Deine Hilfe und Grüße,

Jörg

[Der Martin]

Hallo Jörg,

der User befindet sich im Warenkorb

das stelle ich mir sehr originell vor. Eventuell ein bisschen eng. 😉

Live long and pros healthy,
 Martin

[Rolf B]

Hallo Martin,

sowas seh ich regelmäßig im Supermarkt.

Rolf