Left Join und groupierung führt zu verfälschten Ergebniss

[Heiko]

Ich habe zwei Tabellen,

in der einen sind die normalen Daten und in der zweiten Tabelle die einzelnen Texte, es kann aber öfter vorkommen,
das zu einem Eintrag in der ersten Tabelle in der zweisten Tabelle merhere Einträge vorhanden sind.

Table1
1 andreas
2 bernd
3 carmen
4 detlef

table2

1 dddddddd
1 55555555
1 zzzzzzzz
3 gggggggg
3 ffffffff

Wenn ich
--------------------
$sql = "SELECT a.id, a.name, b.text FROM table1 a LEFT JOIN table2 b ON a.id=b.id ORDER by a.id DESC Limit 20 ";
--------------------
ausführe kommmt

andreas dddddddddd
andreas 5555555555
andreas zzzzzzzzzz
bernd
carmen ggggggggggg
carmen fffffffffff
detelf

Wenn ich
--------------------
$sql = "SELECT a.id, a.name, b.text FROM table1 a LEFT JOIN table2 b ON a.id=b.id GROUP BY b.id ORDER by a.id DESC Limit 20 ";
--------------------
ausführe kommmt

andreas dddddddddd
carmen ggggggggggg

Was ja schon mal gut ist, doch ich möchte gerne folgendes Ergebnis haben:

andreas dddddddddd
bernd
carmen ggggggggggg
detelf

Heiko

[dedlfix]

Tach!

$sql = "SELECT a.id, a.name, b.text FROM table1 a LEFT JOIN table2 b ON a.id=b.id ORDER by a.id DESC Limit 20 ";

Deine PHP-Variablen-Zuweisung führt dazu, dass die Syntaxhervorhebung für SQL nicht richtig arbeiten kann. Außerden ist PHP-Code völlig unwichtig für dein Problem, also zeige bitte nur das reine Statement und nichts was da aus anderen Gründen noch drumherum geschrieben ist.

Wenn ich
SELECT a.id, a.name, b.text FROM table1 a LEFT JOIN table2 b ON a.id=b.id GROUP BY b.id ORDER by a.id DESC Limit 20
ausführe kommmt

... unter MySQL für deine Spalten aus der SELECT-Klausel ein Wert aus irgendeinem zufällig ausgewählten Datensatz. Andere DBMSs lassen nur die Werte in der SELECT-Klausel zu, nach denen gruppiert wurde, zuzüglich Aggregatfunktionen.

andreas dddddddddd
carmen ggggggggggg

Außerdem kommt noch bernd (oder detlef) mit NULL als Text. Deren b.ID ist aufgrund des Left Join und des Nichtvorhandenseins von Datensätzen in der zweiten Tabelle nämlich NULL, wodurch deren Datensätze zu einem zusammengefasst werden. b.id ist also kein sinnvolles Gruppierkriterium

Selektiere die Werte aus der ersten Tabelle und hol dir das relevante Feld aus der zweiten mittels einer korrelierten Unterabfrage (correlated subselect). Wenn es mehr als ein Feld sein soll, so erstelle zunächst eine Abfrage, die die relevanten Werte aus der zweiten Tabelle liefert, ohne die erste zu berücksichtigen. Diese Abfrage kannst du dann als Unterabfrage in der FROM-Klausel der ersten Abfrage einfügen und entsprechend joinen.

dedlfix.

[Heiko]

Hallo dedlfix,

danke für dein ausführliches Statement, doch leider, auch wenn ich mir die Seiten zur Korrelierte Unterabfragen ansehe, ich bekomme  sie nicht hin.

"Wenn es mehr als ein Feld sein soll, so erstelle zunächst eine Abfrage, die die relevanten Werte aus der zweiten Tabelle liefert, ohne die erste zu berücksichtigen. Diese Abfrage kannst du dann als Unterabfrage in der FROM-Klausel der ersten Abfrage einfügen und entsprechend joinen."

Ich brauche also zwei SELECT Abfragen.

Die erste liest normal die Tabelle 1 aus

SELECT a.id, a.name FROM table1 a ORDER BY a.id DESC

Die 2. Select Abfrage Gruppiert zusammen, und es gibt nur einen Eintrag wenn mehrere Felder existieren.

  
SELECT b.text FROM table2 b GROUP BY b.text

So jetzt kommt der Punkt an dem ich es nicht verstehe. Wie bekomme ich die beiden SELECT Abfrage zusammen?

[dedlfix]

Tach!

Ich brauche also zwei SELECT Abfragen.
Die erste liest normal die Tabelle 1 aus
SELECT a.id, a.name FROM table1 a ORDER BY a.id DESC
Die 2. Select Abfrage Gruppiert zusammen, und es gibt nur einen Eintrag wenn mehrere Felder existieren.
SELECT b.text FROM table2 b GROUP BY b.text

Gruppieren ist nicht der richtig Weg, weil du damit nur zufällig und der Laxheit MySQLs dein gewünschtes Ergebenis erhältst.

So jetzt kommt der Punkt an dem ich es nicht verstehe. Wie bekomme ich die beiden SELECT Abfrage zusammen?

Du kannst Subselects an verschiedenen Stellen anwenden. (Ich kürze die

SELECT x, (SELECT einFeld FROM ...) FROM ...

Mit diesem Subselect kannst du genau einen Wert in die Ergebniszeile einfügen, weil an dieser Position immer nur ein Feld angegeben werden kann. Die Subquery darf dann auch nur einen einzelnen Wert als Ergebnis bringen.

SELECT a.x, b.x, b.y, b.z FROM a, (SELECT x, y, z FROM b) b WHERE ...
  SELECT a.x, b.x, b.y, b.z FROM a LEFT JOIN (SELECT x, y, z FROM b) b ON ... WHERE ...

Bei diesen Varianten kannst du mehrere Werte beisteuern, weil an dieser Position normalerweise Tabellen mit egal wievielen Feldern stehen. Wieviele Werte davon genommen werden, entscheidet sich erst in der SELECT-Klausel.

Und nun kommt deine Aufgabenstellung, die du eventuell für das Posten hier gekürzt hast. Wenn du wirklich aus b nur eien Wert brauchst, so wie das in deinen Statements zu sehen ist, wird alles gut. Die Subquery muss aus b die zugehörigen (korrelierenden) Datensätze zur a.id ermitteln, sie nach der Aktualität sortieren und auf 1 limitieren. Von diesem Datensatz gibst du in der SELECT-Klausel genau einen Feldnamen an.

Wenn du stattdessen aus b mehrere Werte benötigst, dann kannst du nur die zweite Variante nehmen. Und nun wird die Sache komplexer als ich es mir bei meiner ersten Antwort auf die Schnelle vorgestellt hatte. Um a mit b zu verknüpfen brauchst zwar bei dieser Variante keine Korrelation sondern nur einen Join und die Joinbedingung, aber wenn wir es mal richtig und ohne Gruppierung machen wollen, brauchen wir nun in der Subquery auf b noch eine weitere Subquery mit Korrelation und auch wieder auf b. Das Prinzip hat Vinzenz schon zu verlinken versucht, was ihm aber misslang: </archiv/2006/7/t133015/#m861544>. Insgesamt sieht das dann so aus:

SELECT a.felder, b.felder
  FROM tabelle1 a LEFT JOIN
    ( SELECT felder FROM tabelle2 b1 WHERE zeit = (SELECT MAX(zeit) FROM tabelle2 b2 WHERE b1.id = b2.id) ) b ON a.id=b.id

Diese Lösung ist aber nicht universell, denn sie setzt voraus, dass der Zeit-Wert über die gesamte Tabelle eindeutig ist. Wenn das bei dir nicht der Fall ist (falls du überhaupt diese Variante benötigst), müsste Vinzenz (oder ein anderer Datenbankexperte) nochmal ran und seinen Ärmel untersuchen, ob er aus selbigem eine bessere Lösung schütteln kann.

dedlfix.

[Sven Rautenberg]

Moin!

Wenn ich

$sql = "SELECT a.id, a.name, b.text FROM table1 a LEFT JOIN table2 b ON a.id=b.id ORDER by a.id DESC Limit 20 ";

ausführe kommmt

andreas dddddddddd
andreas 5555555555
andreas zzzzzzzzzz
bernd
carmen ggggggggggg
carmen fffffffffff
detelf

Die NULL-Werte bei Bernd und Detelf hast du unterschlagen...

Wenn ich

$sql = "SELECT a.id, a.name, b.text FROM table1 a LEFT JOIN table2 b ON a.id=b.id GROUP BY b.id ORDER by a.id DESC Limit 20 ";

ausführe kommmt

andreas dddddddddd
carmen ggggggggggg

Was ja schon mal gut ist, doch ich möchte gerne folgendes Ergebnis haben:

andreas dddddddddd
bernd
carmen ggggggggggg
detelf

Warum? Warum genau dddddddddd bei Andreas, warum genau ggggggggggg bei Carmen? In deinem Query machst du keine Vorschriften, genau diese Infos rauszubekommen, und MySQL lässt das leider zu, ohne dir zu verraten (außer in der Doku), dass beim undefinierten Gruppieren von Spalten irgendein zufälliger Wert gewählt wird, der obendrein bei Änderungen in der DB auch noch wechseln könnte. Dass bei dir dort zufällig "der erste" Wert drinsteht, ist nicht garantiert.

Dann: Durch deine Bedingung zum JOIN ist definiert: a.id == b.id. Du gruppierst nach b.id - warum gruppierst du nicht nach a.id? Die ist schließlich gleich.

- Sven Rautenberg

[Heiko]

Ich verstehe überhaupt nichts mehr und alle meine versuche laufen ins leere:

$sql = "SELECT a.id, a.name, b.text FROM table1 a LEFT JOIN table2 b ON a.id=b.id ORDER by a.id DESC Limit 20 ";

liefert

andreas dddddddddd
andreas 5555555555
andreas zzzzzzzzzz
bernd
carmen ggggggggggg
carmen fffffffffff
detelf

Wie schaffe ich es also, das bernd und detlef weiter angeziegt werden (inklusive NULL Wert)

und das von den anderen immer nur ein Wert, der erste in der erste (der älteste) angezigt wird.

So, jetzt gehe ich auf die Terasse und haue meinen Fuss in irgendwas rein (Tonne), vieleicht hilft das!!!!!

Heiko

[Vinzenz Mai]

Hallo,

Wie schaffe ich es also, das bernd und detlef weiter angeziegt werden (inklusive NULL Wert)

und das von den anderen immer nur ein Wert, der erste in der erste (der älteste) angezigt wird.

dann benötigt Deine zweite Tabelle eine Spalte, die Dir verrät, welcher Wert der älteste ist. Dafür eignet sich ganz hervorragend eine TIMESTAMP-Spalte. Ohne eine solche (oder vergleichbare) Spalte kannst Du den ältesten Wert *nicht* ermitteln.

Wie Du dann vorgehen kannst, könntest Du diesem Archivbeitrag entnehmen:

/archiv/2006/7/t133015/#m861544

Ausgangsmenge wäre bei Dir nicht eine einfache Tabelle, sondern das Ergebnis eines OUTER JOINs.

Freundliche Grüße

Vinzenz