MySQL, JOIN + GROUP BY

[Bodo Thiesen]

Also, meine Anfrage sieht prinzipiell so aus:

SELECT <felder aus t1 und t2>, MIN(t1.wert*t2.wert) FROM tabelle AS t1, tabelle AS t2 GROUP BY t2.name;

Was ich möchte:

Alle <felder aus t1 und t2> sollen ausgegeben werden, dabei sollen aus t1 alle Zeilen angezeigt werden, aus t2 aber nur die, für die das MIN zutrifft. Bisher wird von t2 immer eine (aber die falsche) Zeile ausgegeben, das MIN an sich funktioniert wie gewünscht. Sollten aus t2 mehrere Zeilen zutreffen, ist es mir egal, ob nur eine (und auch welche) oder mehrere ausgegeben werden.

Wenn es irgendwie machbar ist, sollte das ganze in ein Query rein.

Gruß, Bodo

[Vinzenz Mai]

Hallo Bodo,

SELECT <felder aus t1 und t2>, MIN(t1.wert*t2.wert) FROM tabelle AS t1, tabelle AS t2 GROUP BY t2.name;

das ist zunächst ein CROSS JOIN, das heißt:
Jede Zeile aus Tabelle t1 wird mit jeder Zeile aus t2 verknüpft.
Willst Du das wirklich?
Gibt es wirklich keine Join-Bedingung?

Was ich möchte:

Alle <felder aus t1 und t2> sollen ausgegeben werden, dabei sollen aus t1 alle Zeilen angezeigt werden, aus t2 aber nur die, für die das MIN zutrifft.

Verstehe ich Dich richtig: Join-Bedingung ist das Minimum des Produkts. Das erscheint mir wenig schlüssig. Somit müsste grundsätzlich bei jedem Datensatz mit positivem Inhalt in t1.wert die gleiche Zeile aus t2 zugeordnet werden und genauso bei jedem Datensatz mit negativem Inhalt eine andere, aber stets gleiche Zeile, aus t2. Was soll das?

Bisher wird von t2 immer eine (aber die falsche) Zeile ausgegeben, das MIN an sich funktioniert wie gewünscht.

Du begehst einen Kardinalfehler, was MySQL angeht:
MySQL ist dokumentiert großzügig, was die Syntax beim Einsatz von Aggregatsfunktionen und GROUP BY angeht:

Alle Werte in allen Spalten, über die weder gruppiert noch aggregiert wird, sind zufällig. Das möchtest Du wahrscheinlich nicht. Wahrscheinlich ist die Lösung Deines Problems eine korrelierte Unterabfrage. Wenn Du uns ein paar Beispieldatensätze aus Deinen beiden Tabellen (selbstverständlich Spieldaten) lieferst, dazu das gewünschte Ergebnis, dann kann man Dir wesentlich besser helfen.

Freundliche Grüße

Vinzenz

[Bodo Thiesen]

Hallo Vinzenz

SELECT <felder aus t1 und t2>, MIN(t1.wert*t2.wert) FROM tabelle AS t1, tabelle AS t2 GROUP BY t2.name;

Zusätzlich hatte ich davor geschrieben: "Also, meine Anfrage sieht prinzipiell so aus:"

Ich betone das prinzipiell!

das ist zunächst ein CROSS JOIN, das heißt:
Jede Zeile aus Tabelle t1 wird mit jeder Zeile aus t2 verknüpft.
Willst Du das wirklich?
Gibt es wirklich keine Join-Bedingung?

Ja, das will ich und selbstverständlich gibt es auch noch Join-Bedingungen, war aber für das Problem hier eher irrelevant.

Alle <felder aus t1 und t2> sollen ausgegeben werden, dabei sollen aus t1 alle Zeilen angezeigt werden, aus t2 aber nur die, für die das MIN zutrifft.

Verstehe ich Dich richtig: Join-Bedingung ist das Minimum des Produkts. Das erscheint mir wenig schlüssig. Somit müsste [...] Was soll das?

Die konkrete Bedingung ist auch eine andere. Auch hier greift das Argument "prinzipiell". Ich wollte nur die *Struktur* des Querys kommunizieren, nicht den Inhalt.

Bisher wird von t2 immer eine (aber die falsche) Zeile ausgegeben, das MIN an sich funktioniert wie gewünscht.

Du begehst einen Kardinalfehler, was MySQL angeht:
MySQL ist dokumentiert großzügig, was die Syntax beim Einsatz von Aggregatsfunktionen und GROUP BY angeht:

Alle Werte in allen Spalten, über die weder gruppiert noch aggregiert wird, sind zufällig. Das möchtest Du wahrscheinlich nicht.

Exakt aufgefasst.

Wahrscheinlich ist die Lösung Deines Problems eine korrelierte Unterabfrage.

Tatsächlich enthält der Link genau die Lösung zu meinem Problem.

Aber da tut sich mir aber auch gleich das nächste Problem auf.

DROP TABLE IF EXISTS t;
CREATE TEMPORARY TABLE t ([...], d DOUBLE);
INSERT INTO t SELECT [...], t1.wert*t2.wert AS d FROM tabelle AS t1, tabelle AS t2 WHERE [...] HAVING d = ( SELECT MIN(t1.wert*t3.wert) FROM tabelle AS t3 WHERE [...] );
SELECT * FROM t ORDER BY d LIMIT 20;

Funktioniert wunderbar. Mache ich es aber ohne Temptabelle:

SELECT [...], t1.wert*t2.wert AS d FROM tabelle AS t1, tabelle AS t2 WHERE [...] HAVING d = ( SELECT MIN(t1.wert*t3.wert) FROM tabelle AS t3 WHERE [...] ) ORDER BY d LIMIT 20;

bekomme ich keine Ergebnisse mehr. Woran könnte das liegen? In Deinem Beispiel aus dem anderen Thread konnte ich das Verhalten leider nicht reproduzieren.

Gruß, Bodo

[Vinzenz Mai]

Hallo Helmut,

SELECT <felder aus t1 und t2>, MIN(t1.wert*t2.wert) FROM tabelle AS t1, tabelle AS t2 GROUP BY t2.name;

Zusätzlich hatte ich davor geschrieben: "Also, meine Anfrage sieht prinzipiell so aus:"

Ich betone das prinzipiell!

Tut mir leid, aber dieses Prinzip verhindert prinzipiell jede Hilfe.

das ist zunächst ein CROSS JOIN, das heißt:
Jede Zeile aus Tabelle t1 wird mit jeder Zeile aus t2 verknüpft.
Willst Du das wirklich?
Gibt es wirklich keine Join-Bedingung?

Ja, das will ich und selbstverständlich gibt es auch noch Join-Bedingungen, war aber für das Problem hier eher irrelevant.

Doch, die ist relevant. Ganz besonders relevant. Entscheidend. Ich habe überhaupt keine Ahnung, was Du vorhast, was Dein Ziel ist. OK, ich habe jetzt gesehen, dass Du anscheinend einen SELFJOIN machen willst.

Verstehe ich Dich richtig: Join-Bedingung ist das Minimum des Produkts. Das erscheint mir wenig schlüssig. Somit müsste [...] Was soll das?

Die konkrete Bedingung ist auch eine andere. Auch hier greift das Argument "prinzipiell". Ich wollte nur die *Struktur* des Querys kommunizieren, nicht den Inhalt.

Nein, ich kann Dir nicht prinzipiell helfen! Ich kann Dir konkret helfen! Aber nicht mit diesen absolut unzureichenden Angaben. Wahrscheinlich wäre statt des Selfjoins ein Subselect möglich und sinnvoll. Ob das der Fall ist, kann ich nur sagen, wenn ich konkret sehe, was Du vorhast. Dein "prinzipiell" ist ausgesprochen kontraproduktiv.

Ich weiß leider nicht, was Dein Ziel ist - im Gegensatz zu Dir. Also ist es Deine Aufgabe, die notwendigen Angaben zu liefern - und nicht meine, mein Gehirn damit zu strapazieren, was Dein Ziel sein könnte.

Also erneut meine Bitte:

Beispieltabelle (es ist ja nur eine) mit Beispieldatensätzen, mit allen relevanten Spalten, mit den Joinbedingungen. Beispielergebnis mit Begründung.
Kein "prinzipieller Ansatz" eines SQL-Statements. Sowas ist hier nicht hilfreich.

Freundliche Grüße

Vinzenz

[Bodo Thiesen]

Hallo Helmut,

???

Tut mir leid, aber dieses Prinzip verhindert prinzipiell jede Hilfe.

Du hattest den Gegenbeweis bereits angetreten, denn Deine erste Antwort war ja genau die Antwort auf meine Frage.

Nein, ich kann Dir nicht prinzipiell helfen! Ich kann Dir konkret helfen! Aber nicht mit diesen absolut unzureichenden Angaben. Wahrscheinlich wäre statt des Selfjoins ein Subselect möglich und sinnvoll. Ob das der Fall ist, kann ich nur sagen, wenn ich konkret sehe, was Du vorhast. Dein "prinzipiell" ist ausgesprochen kontraproduktiv.

Es gibt manchmal Situationen, in denen man Details der Arbeit nicht veröffentlichen will. Vor allem aber gibt es auch Situationen, in denen man Details der Arbeit nicht veröffentlichen *darf*.

Beispieltabelle (es ist ja nur eine) mit Beispieldatensätzen, mit allen relevanten Spalten, mit den Joinbedingungen. Beispielergebnis mit Begründung.

Gut, ich habe mir jetzt mal Gedanken gemacht, wie ich
1. das Beispiel vereinfachen, dabei
2. von der eigentlich zu lösenden Aufgabe entkoppeln, und dennoch
3. das eigentlich zu lösende Problem beibehalten
kann.

Das folgende in sich vollständige Beispiel zeigt, was ich prinzipiell haben will an einem konkreten Beispiel. Insbesondere zeigt es die in meinem zweiten Post genannte Kuriosität mit dem ORDER BY:

DROP TABLE tabelle;
// Mir wäre hier eine Temporäre Tabelle lieber, aber das führt beim SELECT zu
// folgender Fehlermeldung: ERROR 1137 (HY000): Can't reopen table: 't1'
CREATE TABLE tabelle (n CHAR(1), s TINYINT, w DOUBLE);
INSERT INTO tabelle (n, s, w) VALUES
 ("a",1,3),("b",1,5),("c",0,2),("d",0,7),("e",0,4),("f",0,8);
SELECT t1.n AS n1, t2.n AS n2, (t1.w-t2.w)*(t1.w-t2.w) AS d
FROM tabelle AS t1, tabelle AS t2
HAVING d = (
 SELECT MIN((t1.w-t3.w)*(t1.w-t3.w))
 FROM tabelle AS t3 WHERE t3.s=1
);
// Liefert wie erwartet:
// +------+------+------+
// | n1   | n2   | d    |
// +------+------+------+
// | a    | a    |    0 |
// | c    | a    |    1 |
// | e    | a    |    1 |
// | b    | b    |    0 |
// | d    | b    |    4 |
// | e    | b    |    1 |
// | f    | b    |    9 |
// +------+------+------+
// 7 rows in set (0.01 sec)
//
// Jetzt will ich nach d sortieren. Also gleicher Query + ORDER BY d:
SELECT t1.n AS n1, t2.n AS n2, (t1.w-t2.w)*(t1.w-t2.w) AS d
FROM tabelle AS t1, tabelle AS t2
HAVING d = (
 SELECT MIN((t1.w-t3.w)*(t1.w-t3.w))
 FROM tabelle AS t3 WHERE t3.s=1
) ORDER BY d;
// Autsch:
// +------+------+------+
// | n1   | n2   | d    |
// +------+------+------+
// | e    | d    |    9 |
// | c    | b    |    9 |
// | f    | b    |    9 |
// | d    | e    |    9 |
// | b    | c    |    9 |
// | b    | f    |    9 |
// +------+------+------+
// 6 rows in set (0.00 sec)
//
// Der Ausweichansatz über Temporäre Tabelle ...
CREATE TEMPORARY TABLE t (n1 CHAR(1), n2 CHAR(1), d DOUBLE);
INSERT INTO t
 SELECT t1.n AS n1, t2.n AS n2, (t1.w-t2.w)*(t1.w-t2.w) AS d
 FROM tabelle AS t1, tabelle AS t2
 HAVING d = ( SELECT MIN((t1.w-t3.w)*(t1.w-t3.w)) FROM tabelle AS t3 WHERE t3.s=1 );
SELECT * FROM t ORDER BY d;
// ... funktioniert dann wie gewünscht:
// +------+------+------+
// | n1   | n2   | d    |
// +------+------+------+
// | a    | a    |    0 |
// | b    | b    |    0 |
// | c    | a    |    1 |
// | e    | a    |    1 |
// | e    | b    |    1 |
// | d    | b    |    4 |
// | f    | b    |    9 |
// +------+------+------+
// 7 rows in set (0.00 sec)

Nächste Herausforderung wäre dann noch das entfernen der Doppelten n1="e" Spalte (wobei dies mit der temporären t Tabelle durch ein einfaches GROUP BY n1 zu erreichen wäre, allerdings wollte ich diese ja gerade los werden).

Gruß, Bodo

[Vinzenz Mai]

Hallo Bodo,

Gut, ich habe mir jetzt mal Gedanken gemacht, wie ich

1. das Beispiel vereinfachen, dabei
2. von der eigentlich zu lösenden Aufgabe entkoppeln, und dennoch
3. das eigentlich zu lösende Problem beibehalten
   kann.

ok, an Deinem Beispiel kann ich Dir weiterhelfen.

Das folgende in sich vollständige Beispiel zeigt, was ich prinzipiell haben will an einem konkreten Beispiel. Insbesondere zeigt es die in meinem zweiten Post genannte Kuriosität mit dem ORDER BY:

Nicht kurios, schlicht und einfach erwartungsgemäß. Du scheiterst immer noch an der Schlampigkeit von MySQL.

CREATE TABLE tabelle (n CHAR(1), s TINYINT, w DOUBLE);

INSERT INTO tabelle (n, s, w) VALUES
("a",1,3),("b",1,5),("c",0,2),("d",0,7),("e",0,4),("f",0,8);[code]

OK, eine Tabelle mit 6 Zeilen. Bis dahin ist noch alles richtig.

[code lang=sql]SELECT t1.n AS n1, t2.n AS n2, (t1.w-t2.w)(t1.w-t2.w) AS d
FROM tabelle AS t1, tabelle AS t2
HAVING d = (
SELECT MIN((t1.w-t3.w)(t1.w-t3.w))
FROM tabelle AS t3 WHERE t3.s=1
);

  
Nein, so nicht. Das Ergebnis ist leider mal wieder Zufall. Es sieht richtig aus, aber auch nicht mehr. Jedes andere mir bekannte Datenbankmanagementsystem weist Deine SQL-Statement als syntaktisch fehlerhaft zurück.  
  
Du verwendest HAVING. Damit sieht es so aus, als könntest Du die berechnete Spalte d mit dem Spaltenalias zum Eingrenzen der Ergebnismenge benutzen. In Wirklichkeit sollte da eine WHERE-Klausel stehen. Nur ist es in dieser "dummerweise" nicht möglich, Spalten-Aliasnamen zu verwenden, wie ich heute [schon einmal](https://forum.selfhtml.org/?t=148901&m=966551) erwähnt habe.  
  
Nun zu den Konsequenzen von HAVING:  
[HAVING](http://dev.mysql.com/doc/refman/5.0/en/select.html#id3305657) impliziert, dass alle Spalten der Abfrage entweder eine Aggregatsfunktion aufweisen oder gruppiert werden. Du tust das nicht, somit sind die Werte schlicht und einfach mal wieder zufällig.  
  

> // Liefert wie erwartet:  
> // +------+------+------+  
> // | n1   | n2   | d    |  
> // +------+------+------+  
> // | a    | a    |    0 |  
> // | c    | a    |    1 |  
> // | e    | a    |    1 |  
> // | b    | b    |    0 |  
> // | d    | b    |    4 |  
> // | e    | b    |    1 |  
> // | f    | b    |    9 |  
> // +------+------+------+  
  
Dieses Ergebnis erreichst Du korrekt über folgendes Statement:  
  
~~~sql
SELECT  
    t1.n AS n1,  
    t2.n AS n2,  
    (t1.w-t2.w)*(t1.w-t2.w) AS d  
FROM  
    tabelle t1,    -- nicht jeder SQL-Dialekt erlaubt AS für Tabellen-Aliasnamen  
    tabelle t2     -- deswegen lasse ich AS prinzipiell weg :-)  
WHERE (t1.w-t2.w)*(t1.w-t2.w) = (  -- hier muss der gleiche Ausdruck hin, mit dem d berechnet wird  
    SELECT  
        MIN((t1.w-t3.w)*(t1.w-t3.w))  
    FROM tabelle t3  
    WHERE t3.s=1  
)

Nun ist es auch ganz einfach, richtig zu sortieren. Dabei ist zu beachten, dass Du in der ORDER-BY-Klausel einen Spaltenalias verwenden darfst:

SELECT  
    t1.n AS n1,  
    t2.n AS n2,  
    (t1.w-t2.w)*(t1.w-t2.w) AS d  
FROM  
    tabelle t1,    -- nicht jeder SQL-Dialekt erlaubt AS für Tabellen-Aliasnamen  
    tabelle t2     -- deswegen lasse ich AS prinzipiell weg :-)  
WHERE (t1.w-t2.w)*(t1.w-t2.w) = (  -- hier muss der gleiche Ausdruck hin, mit dem d berechnet wird  
    SELECT         -- korrelierte Unterabfrage  
        MIN((t1.w-t3.w)*(t1.w-t3.w))  
    FROM tabelle t3  
    WHERE t3.s=1  
)  
ORDER BY d

liefert das von Dir gewünschte Ergebnis:

+------+------+------+
| n1   | n2   | d    |
+------+------+------+
| a    | a    |    0 |
| b    | b    |    0 |
| c    | a    |    1 |
| e    | a    |    1 |
| e    | b    |    1 |
| d    | b    |    4 |
| f    | b    |    9 |
+------+------+------+
7 rows in set (0.00 sec)

Ich hoffe, Du kannst dieses Beispiel auf Deinen Bedarf hin extrapolieren.

Freundliche Grüße

Vinzenz

[Bodo Thiesen]

Hallo Vinzenz

Nicht kurios, schlicht und einfach erwartungsgemäß. Du scheiterst immer noch an der Schlampigkeit von MySQL.

Ich glaub' ich steig dann demnächst auf PostgreSQL um, wurde mir sowieso schon mehrfach empfohlen.

Dieses Ergebnis erreichst Du korrekt über folgendes Statement:

WHERE (t1.w-t2.w)*(t1.w-t2.w) = (  -- hier muss der gleiche

Tscha, ... ich war knapp davor, WHERE statt HAVING zu benutzen, aber aus (Tipp-)Faulheit ...

Warum kann man eigentlich in WHERE keine Aliases verwenden? Irgendwo wurde begründet, daß die Spalte zum Zeitpunkt der WHERE-Auswertung noch nicht berechnet worden sei, aber letztlich ist die Begründung doch Quatsch - dann kann man doch den Alias wieder in die Formel umwandeln. Der Mensch macht doch da auch nix anderes. Davon abgesehen bleibt dann noch die Frage, was einfacher zu optimieren wäre.

Nun ist es auch ganz einfach, richtig zu sortieren.

ORDER BY d

Ich hoffe, Du kannst dieses Beispiel auf Deinen Bedarf hin extrapolieren.

Ja, wunderbar, danke Dir.

Gruß, Bodo