Maximalwert aus einer 1:n-Beziehung ermitteln

J

janw

Mitglied
Hallo zusammen,

ich habe folgendes Szenario: Eine Stammdaten-Tabelle mit Benutzern, die Aktionen ausführen können, und eine Tabelle mit konkreten, ausgeführten Aktionen dieser Benutzer mit der Fremdschlüssel-Beziehung, dass jede Aktion zu genau einem Benutzer gehört. Vereinfacht:
BENUTZER(id NUMBER, name VARCHAR(30))
AKTION(id NUMBER, ben_id NUMBER, zeitpunkt DATE, beschreibung VARCHAR(30), ...noch ein paar weitere Spalten)

Mein Ziel ist nun, mit einer Abfrage alle Benutzer zusammen mit Zeitpunkt und Beschreibung ihrer aktuellsten Aktion zu erhalten. Falls ein Benutzer noch keine Aktion ausgeführt hat, sollen Nullwerte verwendet werden. Es sollte examplarisch also etwa so aussehen (zeigt Benutzer 4711 und 42 mit ihren zuletzt ausgef. Aktionen, Benutzer 23 ist noch ohne Aktion):

Spalten: (Benutzer.id, Benutzer.name, Aktion.zeitpunkt, Aktion.beschreibung)
[tab](4711, Peter, 01.08.2005 11:12, Neue Aktion)[/tab]
[tab](42, James, 22.07.2005 22:30, Andere Aktion)[/tab]
[tab](23, LazyJoe, NULL, NULL)[/tab]

Ich kann dieses Problem lösen: mit UNION, das die Menge der Benutzer mit bereits mind. einer Aktion vereint mit der Menge von Benutzern ohne Aktionen.

Meine Frage ist jetzt, ob es für dieses nicht außergeöhnliche Problem nicht eine elegantere/perfomantere Lösung gibt?!

Hier ist die Abfrage:
Code: 
SELECT b.name, a.id, a.zeitpunkt
      FROM aktion a, benutzer b
      WHERE a.ben_id = b.id 
      AND a.zeitpunkt = 
          (select max(a2.zeitpunkt) from aktion a2 where a2.ben_id = b.id)
      UNION 
      SELECT b.name, to_number(null), to_date(null)
      FROM benutzer b
      WHERE NOT EXISTS(
          select *
          FROM aktion a
          WHERE a.ben_id = b.id
          )
          ORDER BY 1;


Für Ideen dazu sehr dankbar...

Gruß
Jan
 
Ich bin mir nicht sicher, ob ich Dich richtig verstanden habe (Dein Query verwirrt mich), aber für mich schreit Deine Beschreibung nach einem LEFT JOIN:
Code: 
SELECT 
  b.name, 
  a.id, 
  a.zeitpunkt
FROM benutzer AS b
LEFT JOIN aktion AS a
  ON a.ben_id=b.id
WHERE 
  a.zeitpunkt = (
    SELECT 
      MAX(a2.zeitpunkt)
    FROM aktion AS a2 
    WHERE a2.ben_id = b.id
  )
Ist es das?

Gruß hpvw
 
Hi,
danke für den Vorschlag. Das DBMS ist Oracle 8.1.7. Daher sieht der LEFT JOIN unten auch ein wenig altertümlich aus.
Wenn ich die Abfrage so ausführe, bekomme ich nur die Benutzer, die auch eine Aktion ausgeführt haben - anscheinend wegen der Bedingung
WHERE a.zeitpunkt = (SELECT ...,
die ja nicht zutrifft.
Ich möchte aber alle Benutzer bekommen...

Code: 
   SELECT b.name, a.id, a.zeitpunkt
   FROM benutzer b, aktion a
   WHERE  (a.ben_id(+) = b.id) AND
     a.zeitpunkt = (
   	SELECT 
   	  MAX(a2.zeitpunkt)
   	  FROM aktion a2 
   	  WHERE a2.ben_id = b.id
     );
 
Ist das einfache Verknüpfen mit Komma bei Oracle ein LEFT JOIN?
Trotz allem habe ich natürlich etwas vergessen, sorry.
Bei dem WHERE sollte man natürlich noch etwas ergänzen:
Code: 
   WHERE  (a.ben_id(+) = b.id) AND (
     a.zeitpunkt = (
   	SELECT 
   	  MAX(a2.zeitpunkt)
   	  FROM aktion a2 
   	  WHERE a2.ben_id = b.id
     )
     OR a.zeitpunkt IS NULL # <-Auf Oracle-Syntax ggf. anpassen
   );
Das funktioniert natürlich nur sicher, wenn a.zeitpunkt ein "NOT-NULL-Feld" ist.

Gruß hpvw
 
Das sieht gut aus...

Yes,
wenn mich nicht alles täuscht, dann war das der entscheidende Tipp. Vielen Dank!

Zu deiner Anmerkung:
> Das funktioniert natürlich nur sicher, wenn a.zeitpunkt ein "NOT-NULL-Feld" ist.

Verstehe ich dich richtig: Falls a.zeitpunkt NULL-Werte zulässt, und für Benutzer 4711 eine Aktion / nur Aktionen ohne Zeitpunkt eingetragen ist/sind, dann sähe es so aus, als ob Benutzer 4711 noch keine Aktionen ausgeführt hätte. ?

Zur Oracle-Syntax:
Das Komma bildet wie z.B. bei mysql auch das Kreuzprodukt zwischen zwei Tabellen.
Der LEFT JOIN kommt (AFAIK nur bis 8.1.7) durch (+) zustande, wie bei
(a.ben_id(+) = b.id).

Gruß
Jan
 
Re: Das sieht gut aus...

janw hat gesagt.:
Verstehe ich dich richtig: Falls a.zeitpunkt NULL-Werte zulässt, und für Benutzer 4711 eine Aktion / nur Aktionen ohne Zeitpunkt eingetragen ist/sind, dann sähe es so aus, als ob Benutzer 4711 noch keine Aktionen ausgeführt hätte. ?
Zum Vergrößern anklicken....
In dem Fall sollte sowohl die Zeile mit der letzten Aktion, als ich die Zeilen, in denen a.zeitpunkt NULL ist in die Ergebnistabelle aufgenommen werden, sofern zu dem Benutzer Zeilen mit NULL-Werten existieren.

janw hat gesagt.:
Zur Oracle-Syntax:
Das Komma bildet wie z.B. bei mysql auch das Kreuzprodukt zwischen zwei Tabellen.
Der LEFT JOIN kommt (AFAIK nur bis 8.1.7) durch (+) zustande, wie bei
(a.ben_id(+) = b.id).
Zum Vergrößern anklicken....
Die Syntax ist ja etwas kryptisch. Das kleine Plus habe ich gar nicht gesehen, sorry für die überflüssigen Kommentare.

Gruß hpvw
 
Hi,

ich kann mich täuschen, aber in 8.1.7 sollte auch der Left-Join auch direkt mit dem Begriff funktionieren und nicht nur mit dem (+). Soweit ich weiß, sind die Koin-Begriffe SQL-spezifisch und nicht DBMS-spezifisch.
Wie gesagt, ich kann mich täuschen, aber ich bin der Meinung, dass es damit auch funktioniert.
Bitte um Richtigstellung, falls es nicht so sein sollte.
 
Bleibt nur die Frage, welches DBMS die in SQL definierten Befehle so implementiert, wie sie definiert sind. Jedes DBMS hat halt irgendwo Abweichungen oder Erweiterungen von der allgemeinen Definition in seinem Dialekt. Die Ähnlichkeit ist aber dank des Standards zumindest so hoch, dass man sich schnell in ein anderes DBMS einarbeiten kann.

Gruß hpvw
 
Keywords wie INNER JOIN, LEFT JOIN oder FULL OUTER JOIN sind im sog. ANSI SQL/92 Standard definiert den mittlerweile jede "SQL konforme" Datenbank aus konsequent umsetzen sollte (sofern die erwähnten Features auch unterstützt werden).

Auf Grund des Alters ist Oracle in der Version 8.1.7 nur SQL/86 (od. SQL/89?) konform. Daher die etwas umständliche Syntax mit dem (+).
In einem Join die beiden Tabellen mit Komma zu verknüpfen ist übrigens auch nicht mehr ANSI konform, wird jedoch i.d.R. als INNER JOIN interpretiert, sofern in der WHERE Klausel die entsprechenden Bedingungen gesetzt sind. Ansonsten entsteht ein Kartesisches Produkt.
Mittlerweile sind wir beim SQL/2003 Standard der u.a. XML integriert und Mengenoperationen wie MERGE und MULTISET.
 
Hi,
ärgerlich wird's dann, wenn die SQL-Statements fest im Code sind und man wegen einer Umstellung von 8.1.7 auf 9i die SQL-Statements anpassen muss und den Code neu deployen muss...kurzfristig gedacht :(

Danke f. die rege Beteiligung

Gruß
Jan
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück