[Erledigt] LEFT JOIN Query Bildung

03.11.2005, 23:47

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:

Code:

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:

Code:

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:

Code:

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 · 04.11.2005, 01:04

Zitat:

Funktioniert es mit einer Abfrage?

meiner meinung nach ja.

Zitat:

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

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

Zitat:

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 ...

04.11.2005, 01:59

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:

Code:

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:

Code:

ProcessID    Name    WarningID  Status

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

Bis morgen

derHund · 04.11.2005, 03:42

diese query

Code:

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 ...

04.11.2005, 14:14

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:

Code:

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:

Code:

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 · 04.11.2005, 14:34

Zitat:

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

hast du die query mal mit dieser situation getestet?

Zitat:

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.

04.11.2005, 15:24

Zitat:

hast du die query mal mit dieser situation getestet?

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

Code:

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:

Code:

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

mein "Workaround":

Code:

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

Zitat:

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...

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
WHERE-Clause im Left Join	cycap	Datenbanken	2	06.11.2007 11:06
JOIN / LEFT JOIN und Co	prinzli	Datenbanken	12	05.11.2007 22:15
LEFT OUTER JOIN auf Nicht-Tabelle	inu	Datenbanken	15	29.03.2007 20:21
[solved] LEFT JOIN - #1066 - Not unique table/alias	moose	Datenbanken	6	12.12.2006 06:41
[gelöst] LEFT JOIN und trotzdem nicht alle Sätze da?!?	stefanjann	Datenbanken	3	24.10.2006 11:32
Problem mit Inner join	Jabi	Datenbanken	15	09.05.2006 14:10
Left outer join, timestamps	php-newbie83	Datenbanken	29	27.04.2006 09:29
LEFT OUTER JOIN für mehr als 2 Tabellen ?	Alpha Centauri	Datenbanken	4	05.04.2006 16:18
Select Problem		Datenbanken	17	16.01.2006 21:54
[Erledigt] LEFT JOIN funtkioniert nicht richtig		Datenbanken	3	19.10.2005 17:38
[Erledigt] LEFT JOIN Problem		Datenbanken	11	21.03.2005 16:36
[Erledigt] Menülinks in Kategorien per Left join		Datenbanken	20	14.03.2005 12:43
[Erledigt] left outer join mit bedingung		Datenbanken	2	04.03.2005 00:52
left outer join Problem		Datenbanken	5	21.01.2005 17:25
den letzte(grösten) eintrag aus LEFT JOIN		Datenbanken	2	24.07.2004 18:33

03.11.2005, 23:47
Gast Beiträge: n/a	[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: Code: 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: Code: 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: Code: 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


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

04.11.2005, 14:14
Gast Beiträge: n/a	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: Code: 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: Code: 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.