[Erledigt] LEFT JOIN Query Bildung

Hallo,

SQL-Queries jenseits von simpelsten SELECT, UPDATE, etc. Befehlen sind Neuland für mich. Deswegen stoße ich jetzt vor folgendem Problem an meine Grenzen:

Ich habe 2 Tabellen mit folgenden (für dieses Problem relevanten) Feldern:

Tabelle processes

ProcessID
Name
Active

Tabelle warnings

WarningID
ProcessID
Status //nimmt Werte von 0..4 an, wobei 4 der worst-case ist
Acknowledged //ist 0 wenn die Warnung noch nicht bearbeitet wurde


Mein Ziel ist: Eine vollständige Liste aller aktiven Prozesse zu erhalten wobei zusätzlich jeweils die Warnung mit dem schlechtesten Status angegeben wird, welche noch nicht bearbeitet wurde.

Bsp.:

Ich habe 5 Prozesse wobei der 3. nicht aktiv ist. An Warnungen hab ich z.B.

für Prozess 1 einen Status 3 und einen Status 4,
für Prozess 2 einen Status 2,
für Prozess 4 einen Status 1 und einen Status 3,
für Prozess 5 keine offenen Warnung.


Mein Wunschergebnis sollte also so aussehen:

ProcessID WarningID Status

   1         x         4
   2         x         2
   4         x         3
   5        NULL      NULL

Mittlerweile habe ich in Erfahrung gebracht, dass LEFT JOIN eventuell mein Freund ist. Folgende Abfrage hab ich mir inzwischen zusammenreimen können:

SELECT
  processes.ProcessID, processes.Name, warnings.WarningID, warnings.Status
FROM 
  processes
LEFT JOIN
  warnings ON (processes.ProcessID = warnings.ProcessID)
WHERE
  processes.Active = 1 AND warnings.Acknowledged = 0

Nun hat diese Abfrage zwei Probleme: Einerseits werden alle Status zu einem Prozess ausgegeben, andererseits werden Prozesse für die es bearbeitete Warnungen aber keine offenen gibt, vollständig unterdrückt und nicht in der "rechten Tabelle" mit null befüllt. Das Ergebnis für o.g. Beispiel sähe also so aus:

ProcessID WarningID Status

   1         x         3
   1         x         4
   2         x         2
   4         x         1
   4         x         3

Momentan suche ich also nach einer Lösung hierfür. Funktioniert es mit einer Abfrage? Ev. Subqueries?
Besten Dank im vorraus für jegliche Denkanstöße!

lg,
peter

[derHund]

Funktioniert es mit einer Abfrage?

meiner meinung nach ja.

und nicht in der "rechten Tabelle" mit null befüllt.

damit du NULL erhälst, muß diese bedingung zwingend in den ON-teil

Einerseits werden alle Status zu einem Prozess ausgegeben,

gruppiere auf dem prozeß und verwende max, wenn das nicht geht, http://dev.mysql.com/doc/mysql/en/ex...group-row.html

btw: verwende mal aliase, dann mußt du weniger tippen ...

falls dus nicht hinbekommst, poste mal nen exemplarischen dump zum selber-testen ...

danke schon mal für den Link. Aufgrund der fortgeschrittenen Zeit geht aber momentan glaub ich bei mir gar nichts mehr . Werde mir das morgen nochmal bei klarem Kopf durchlesen.

Bis jetzt bin ich bei genau dieser Teilaufgabe gescheitert (eine Abfrage zu bilden die den "Status-Maximum" pro Prozess ausgibt). Sollte aber mit Hilfe des Links kein Problem sein...


Falls wer Zeit und Muße hat ( ) hier mal der Dump der Test-DB:

CREATE TABLE `processes` (
  `ProcessID` smallint(5) unsigned NOT NULL auto_increment,
  `Name` varchar(255) NOT NULL,
  `Active` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`ProcessID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;


INSERT INTO `processes` VALUES (1, 'Process1', 1);
INSERT INTO `processes` VALUES (2, 'Process2', 1);
INSERT INTO `processes` VALUES (3, 'Process3', 1);
INSERT INTO `processes` VALUES (4, 'Process4', 0);
INSERT INTO `processes` VALUES (5, 'Process5', 1);


CREATE TABLE `warnings` (
  `WarningID` mediumint(8) unsigned NOT NULL auto_increment,
  `ProcessID` smallint(5) unsigned NOT NULL default '0',
  `Status` tinyint(4) unsigned NOT NULL default '0',
  `Acknowledged` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`WarningID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;


INSERT INTO `warnings` VALUES (1, 1, 4, 0);
INSERT INTO `warnings` VALUES (2, 2, 2, 0);
INSERT INTO `warnings` VALUES (3, 2, 4, 0);
INSERT INTO `warnings` VALUES (4, 3, 4, 2);
INSERT INTO `warnings` VALUES (5, 4, 4, 0);
INSERT INTO `warnings` VALUES (6, 1, 4, 1);
INSERT INTO `warnings` VALUES (7, 4, 1, 0);

Soll-Ergebnis ist das hier:

ProcessID    Name    WarningID  Status

   1       Process1     1         4
   2       Process2     3         4
   3       Process3    NULL      NULL
   5       Process5    NULL      NULL

Bis morgen

[derHund]

diese query

SELECT    p.ProcessID,
          p.Name,
          w1.WarningID,
          w1.Status
FROM      processes p
LEFT JOIN warnings w1
ON        p.ProcessID = w1.ProcessID
AND       w1.Acknowledged = 0
LEFT JOIN warnings w2
ON        w2.status > w1.status
WHERE     p.active > 0
AND       w2.ProcessID IS NULL
ORDER BY  p.ProcessID

liefert mit deinen daten das gewünschte ergebnis ...

mhm, das bringt mich schon mal ein Stück weiter, danke.

Aber :

Wenn ich z.B. nur einen nicht bearbeiteten Status für einen Prozess habe, fällt dieser Prozess aus dem Ergebnis heraus (da es ja dann keinen "größer" Vergleich im zweiten LEFT JOIN mehr gibt, wenn ich das richtig interpretiert habe).

Inzwischen bin ich zu folgender Lösung gekommen:

Ich suche mir zuerst aus der Tabelle warnings den maximalen Status pro "Prozessgruppe" heraus:

SELECT
  w2.WarningID, w2.ProcessID, MAX(w2.Status) AS StatusMax
FROM
  warnings w2
WHERE
  w2.Acknowledged = 0
GROUP BY
  w2.ProcessID

Danach reicht ein simples LEFT JOIN der Prozesstabelle mit diesem Ergebnis um alle Prozesse aufgelistet zu bekommen, wobei die "fehlerlosen" automatisch mit NULL ergänzt werden:

SELECT
  p.ProcessID, p.Name, w1.WarningID, w1.StatusMax
FROM
  processes p
LEFT JOIN
  (SELECT
     w2.WarningID, w2.ProcessID, MAX(w2.Status) as StatusMax
   FROM
     warnings w2
   WHERE
     w2.Acknowledged = 0
   GROUP BY
     w2.ProcessID) w1
ON
  p.ProcessID = w1.ProcessID
WHERE
  p.Active = 1

Gibt es für diese Abfrage irgendwelche Verbesserungsvorschläge? Immerhin sind es ja zwei und wie du gesagt hast sollte es auch mit einer funktionieren.

[derHund]

Wenn ich z.B. nur einen nicht bearbeiteten Status für einen Prozess habe,

hast du die query mal mit dieser situation getestet?

fällt dieser Prozess aus dem Ergebnis heraus (da es ja dann keinen "größer" Vergleich im zweiten LEFT JOIN mehr gibt, wenn ich das richtig interpretiert habe).

denn genau dort leigt der hund begraben. es _soll_ keinen datensatz geben, der größer ist. wenn also kein datensatz existiert, der für diese spalte einen größeren wert liefert, dann erhalten wir NULL für w2 (das ist das, was wir wollen). wir nehmen jetzt nur die ds, wo w2 NULL ist, und haben somit ... weißt? schau nochmal ins manual (o.g. links), das ganze prinzip beruht auf dem, was du monierst ...

sollte es wider erwaretn nicht funktionieren, dann poste nochmal die daten, mit denen es zu fehlern kommt.

hast du die query mal mit dieser situation getestet?

ja, mit folgenden Einträgen für warnings habe ich getestet:

INSERT INTO `warnings` VALUES (1, 1, 4, 0);
INSERT INTO `warnings` VALUES (2, 2, 2, 0);
INSERT INTO `warnings` VALUES (3, 2, 4, 1);
INSERT INTO `warnings` VALUES (4, 3, 3, 2);
INSERT INTO `warnings` VALUES (5, 4, 2, 0);
INSERT INTO `warnings` VALUES (6, 1, 4, 0);
INSERT INTO `warnings` VALUES (7, 4, 1, 0);
INSERT INTO `warnings` VALUES (8, 1, 3, 0);

d.h. es gibt folgende nicht bearbeitete Statusmeldungen:

für Prozess 1: Status 4, 4, 3 -> Status 4 soll angezeigt werden
für Prozess 2: Status 2 -> Status 2 soll angezeigt werden
für Prozess 3: keine offenen -> NULL
für Prozess 4: deaktiviert, soll nicht in Ergebnis vorkommen
für Prozess 5: kein Eintrag -> NULL

dein Query liefert:

ProcessID  Name   WarningID Status
   1     Process1    6        4	
   1     Process1    1        4	
   3     Process3   NULL     NULL	
   5     Process5   NULL     NULL

mein "Workaround":

ProcessID  Name   WarningID Status
   1     Process1    1        4	
   2     Process2    2        2	
   3     Process3   NULL     NULL	
   5     Process5   NULL     NULL

wir nehmen jetzt nur die ds, wo w2 NULL ist, und haben somit ... weißt? schau nochmal ins manual (o.g. links)

genau das werd ich jetzt tun ev. komm ich ja noch dahinter wie dein Query arbeitet...