MySQL Sub-Sub-Select: Aktuelle ID?

[gdvlive]

Servus,

ich habe ein Problem mit MySQL (5.0) und einem Sub-Sub-Select.
Es geht darum, dass ich eine Suche mit Filtern in PHP programmiert habe. Bei den Filtern kann man einstellen, ob man z.B. nur grüne Elemente finden möchte.

Bei meiner Suche geht es explizit darum, dass ich nach Geschäften suche, die eine bestimmte Bewertung bekommen: Man kann einstellen, ob man jetzt nur Geschäfte angezeigt bekommt, die eine bessere Bewertung als 3 Sterne haben (5-Sterne-System). Nun habe ich versucht, diese Abfrage mit einem Sub-Sub-Select zum Laufen zu bringen, jedoch wird mir der Durchschnitt aller Geschäfte berechnet und verglichen. Wie kann ich jetzt in dem Subselect auf das aktuelle Element der höheren Ebene zugreifen?

Hier mal das (aufs wesentliche gekürzte) Statement:

SELECT ge_id, art_id  
FROM (  
  SELECT g.ge_id, ge_name  
  FROM geschaeft AS g  
  WHERE (g.art_id = 1)  
  AND (  
    SELECT AVG(rd_dauer)  
    FROM rating  
    WHERE ge_id = '?????'  
  ) >= 3  
)  
WHERE ge_name LIKE '%Sucheingabe%'  
ORDER BY art_id ASC, ge_name ASC

An der Stelle mit dem '?????' liegt wie gesagt mein Problem: Wie kann ich hier feststellen und der Anweisung übergeben, welche ID die 2. Ebene gerade überprüft?

Vielen Dank im Voraus für alle Ansätze und Hilfen,
Markus

(PS: Ja, ich habe bereits mehrere Leute gefragt und auf bekannten Suchmaschinen recherchiert, aber keiner konnte mir wirklich helfen.)

[Vinzenz Mai]

Hallo Markus,

Bei meiner Suche geht es explizit darum, dass ich nach Geschäften suche, die eine bestimmte Bewertung bekommen: Man kann einstellen, ob man jetzt nur Geschäfte angezeigt bekommt, die eine bessere Bewertung als 3 Sterne haben (5-Sterne-System). Nun habe ich versucht, diese Abfrage mit einem Sub-Sub-Select zum Laufen zu bringen, jedoch wird mir der Durchschnitt aller Geschäfte berechnet und verglichen.

warum verwendest Du die Aggregatsfunktion AVG()?

Wie kann ich jetzt in dem Subselect auf das aktuelle Element der höheren Ebene zugreifen?

Eine Beispieltabelle mit ein paar Beispieldatensätze und dazu das gewünschte Ergebnis mit der Begründung, warum dies das gewünschte Ergebnis wäre hilfreicher als Dein Versuch eines SQL-Statements.

Zur Verknüpfung: so etwas nennt man korrelierte Unterabfragen. Beispiele dafür gibt es im hiesigen Archiv massenhaft, zum Beispiel </archiv/2006/7/t133015/#m861544>.

Freundliche Grüße

Vinzenz

[gdvlive]

Hallo Vinzenz,

erstmal danke für deine schnelle Antwort (die war sogar zu schnell für mich ;)

Bei meiner Suche geht es explizit darum, dass ich nach Geschäften suche, die eine bestimmte Bewertung bekommen: Man kann einstellen, ob man jetzt nur Geschäfte angezeigt bekommt, die eine bessere Bewertung als 3 Sterne haben (5-Sterne-System). Nun habe ich versucht, diese Abfrage mit einem Sub-Sub-Select zum Laufen zu bringen, jedoch wird mir der Durchschnitt aller Geschäfte berechnet und verglichen.

warum verwendest Du die Aggregatsfunktion AVG()?

Wie kann ich jetzt in dem Subselect auf das aktuelle Element der höheren Ebene zugreifen?

Eine Beispieltabelle mit ein paar Beispieldatensätze und dazu das gewünschte Ergebnis mit der Begründung, warum dies das gewünschte Ergebnis wäre hilfreicher als Dein Versuch eines SQL-Statements.

Ich verwende AVG, da ich den Durchschnitt der Bewertungen auslese. Die Bewertungen werden von jedem User pro Geschäft gespeichert.

Hier mal die Versuche von Beispieltabellen:

Tabelle: geschaeft
+-------+--------+---------+
| ge_id | art_id | ge_name |
+-------+--------+---------+
|     1 |      4 | Hallo   |
|     2 |      2 | Test    |
+-------+--------+---------+

Tabelle: rating
+-------+--------+---------+
| ge_id | usr_id | ra_wert |
+-------+--------+---------+
|     1 |      3 |       4 |
|     1 |      7 |       2 |
|     2 |      5 |       1 |
+-------+--------+---------+

Bei meiner Datenbankabfrage versuche ich die ge_id und die art_id auszulesen, von z.B. Geschäften mit einer durchschnittlichen Bewertung von größer gleich als 3 Sternen.
Das Problem, das ich habe ist folgendes: Wenn ich einfach in der Abfrage AND (SELECT AVG(ra_wert) FROM rating) >= 3 mache, dann bekomme ich kein Ergebnis, da hier der Durchschnitt ›aller‹ Geschäfte ausgelesen wird. Ich bräuchte aber, wenn ich überprüfe ob ge_id=1 meinen Kriterien entspricht, den Durchschnittswert von ra_wert nur für die entsprechende ge_id (also 1).

Ich hoffe, du hast jetzt verstanden, was ich meine... ;)

Zur Verknüpfung: so etwas nennt man korrelierte Unterabfragen. Beispiele dafür gibt es im hiesigen Archiv massenhaft, zum Beispiel </archiv/2006/7/t133015/#m861544>.

Die korrelierten Unterabfragen hab ich auch schon gefunden, jedoch wusste ich nicht, was ich dann hier speziell angeben müsste.

Vielen Dank im Voraus,
Markus

[Vinzenz Mai]

Hallo Markus,

Tabelle: geschaeft
+-------+--------+---------+
| ge_id | art_id | ge_name |
+-------+--------+---------+
|     1 |      4 | Hallo   |
|     2 |      2 | Test    |
+-------+--------+---------+

Tabelle: rating
+-------+--------+---------+
| ge_id | usr_id | ra_wert |
+-------+--------+---------+
|     1 |      3 |       4 |
|     1 |      7 |       2 |
|     2 |      5 |       1 |
+-------+--------+---------+

Bei meiner Datenbankabfrage versuche ich die ge_id und die art_id auszulesen, von z.B. Geschäften mit einer durchschnittlichen Bewertung von größer gleich als 3 Sternen.

Das ist doch gar kein Problem :-)

1. Schritt: Join der beiden Tabellen:

  
SELECT             -- Gib mir  
    g.ge_id,       -- die ID der Geschäfte,  
    g.art_id,      -- ihre Art,  
    g.ge_name,     -- ihren Namen und  
    r.usr_id,      -- den Benutzer, der eine Bewertung vorgenommen hat und  
    r.ra_wert      -- die Bewertung  
FROM               -- aus den Tabellen  
    geschaefte g   -- geschaefte  
                   -- Aliasnamen zum besseren Zugriff auf die Tabellen  
INNER JOIN         -- die mit der Tabelle  
    rating r       -- rating (der Bewertungen)  
ON                 -- über die Bedingung  
    g.ge_id = r.ge_id  -- gleicher ge_id verknüpft ist  

Ergebnis:

+-------+--------+---------+--------+---------+
 | ge_id | art_id | ge_name | usr_id | ra_wert |
 +-------+--------+---------+--------+---------+
 |     1 |      4 | Hallo   |      3 |       4 |
 |     1 |      4 | Hallo   |      7 |       2 |
 |     2 |      2 | Test    |      5 |       1 |
 +-------+--------+---------+--------+---------+

2. Schritt:
Gib mir die Geschäfte samt durchschnittlicher Bewertung.
Die Benutzer-Id interessiert beim Mittelwert nicht mehr.

  
SELECT  
    g.ge_id,  
    g.art_id,  
    g.ge_name,  
    AVG(r.ra_wert) AS rating  
                   -- Achtung, Aggregatsfunktion, daher GROUP BY erforderlich  
                   -- es muss nach allen anderen Spalten gruppiert werden  
FROM  
    geschaefte g   -- Aliasnamen zum besseren Zugriff auf die Tabellen  
INNER JOIN  
    rating r  
ON  
    g.ge_id = r.ge_id  
GROUP BY  
    g.ge_id,  
    g.art_id,  
    g.ge_name  

Ergebnis:

+-------+--------+---------+--------+
 | ge_id | art_id | ge_name | rating |
 +-------+--------+---------+--------+
 |     1 |      4 | Hallo   |      3 |
 |     2 |      2 | Test    |      1 |
 +-------+--------+---------+--------+

3. Schritt:
Du möchtest nur die haben, deren Mittelwert größer gleich 3 ist.
Dazu ist die HAVING-Klausel zuständig:

  
SELECT               -- Gib mir  
    g.ge_id,         -- die ID der Geschäfte  
    g.art_id,        -- ihre Art     (direkt von der ge_id abhängig)  
    g.ge_name,       -- ihren Namen  (direkt von der ge_id abhängig)  
    AVG(r.ra_wert) AS rating  
                     -- und den Durchschnitt ihrer Bewertungen  
                     -- Achtung, Aggregatsfunktion, daher GROUP BY erforderlich  
                     -- es muss nach allen anderen Spalten gruppiert werden  
FROM                 -- aus der Tabelle  
    geschaefte g     -- geschaefte, die über den Aliasnamen g angesprochen wird  
INNER JOIN           -- und verknüpft ist mit der Tabelle  
    rating r         -- rating, die über r angesprochen wird  
ON                   -- mit der Bedingung  
    g.ge_id = r.ge_id  -- dass die ge_id-Werte übereinstimmen  
GROUP BY             -- gruppiert, d.h. je Gruppe nur ein Wert, nach  
    g.ge_id,         -- Identifikation des Geschäfts  
    g.art_id,        -- Art des Geschäfts  
    g.ge_name        -- Name des Geschäfts  
HAVING               -- wobei die durchschnittliche Bewertung mindestens den  
    rating >= 3      -- Wert 3 haben muss  
                     -- Du kannst hier auch einen Aliasnamen verwenden im  
                     -- Gegensatz zur WHERE-Klausel, die dies nicht erlaubt.  

liefert das gewünschte Ergebnis:

+-------+--------+---------+--------+
 | ge_id | art_id | ge_name | rating |
 +-------+--------+---------+--------+
 |     1 |      4 | Hallo   |      3 |
 +-------+--------+---------+--------+

Überhaupt keine Subselects nötig, liefe sogar unter MySQL 3.23 :-)
Zur HAVING-Klausel gibt es einen Artikel in SELFHTML aktuell:
Datensätze gruppieren mit SQL.

Freundliche Grüße

Vinzenz

[gdvlive]

Hallo Vinzenz,

Das ist doch gar kein Problem :-)

jetzt wo du's sagst - also ich bin direkt baff, dass es so einfach geht. (In Zukunft eine Tasse mehr Kaffee pro Tag)
Ich bau das ganze noch in den Rest des Codes ein und dann sollte (nein: wird) das auch gehen. ;)

Überhaupt keine Subselects nötig, liefe sogar unter MySQL 3.23 :-)
Zur HAVING-Klausel gibt es einen Artikel in SELFHTML aktuell:
Datensätze gruppieren mit SQL.

Vielen Dank für die verständliche und umfangreiche Antwort! Den Having-Befehl kannte ich noch gar nicht...

Freundliche Grüße,
Markus