MySQL und Subselects

[heinzflohe]

Hallo an alle!

Habe da eine SQL-Abfrage mit einem Subselect:

SELECT InteressentenNr FROM interessentenundobjekte WHERE InteressentenNr NOT IN (SELECT InteressentenNr FROM interessentenundobjekte WHERE ObjektNr = '331') AND ObjektNr = '330'

funktioniert prima. Das Ergebnis ist richtig.

Mein Problem ist jetzt die Abfrage ohne Subselect zu schreiben, so daß es auch mit MySQL-Versionen unter 4.1 funktioniert.

Ich habe da irgendwie einen JOIN-Knoten im Hirn.
Danke schon mal für Hilfe, Tipss, etc.

Grüße aus München

heinzflohe

[Rouven]

Hi,

sowas funktioniert über einen LEFT JOIN und eine Prüfung, ob der Datensatz paarig war oder eben nicht.

SELECT t1.InteressentenNr AS ergebnis, t2.interessentennr AS pruefung
FROM interessentenundobjekte t1 LEFT JOIN interessenundobjekte t2
ON t1.interessentennr = t2.interessentennr
WHERE t2.objektnr = '331'
  AND pruefung IS NULL
  AND t1.objektnr = '330'

Meine ich, weder probiert noch sonderlich lange drüber nachgedacht...

MfG
Rouven

[Ilja]

yo,

Mein Problem ist jetzt die Abfrage ohne Subselect zu schreiben, so daß es auch mit MySQL-Versionen unter 4.1 funktioniert.

das wird meiner meinung mit einen JOIN nach schwierig. der join wird letztlich jeweils immer nur zwei datensätze gleichzeitig miteinander verbinden können. was du brauchst sind aber aussagen, über alle möglichen datensätze der ObjektNr = '331'. es läßt sich vielleicht was tricksen und mit GROUP BY arbeiten. dann wäre es aber von vorteil, dein daten-design besser zu kennen.

Ilja

[heinzflohe]

Hallo Ilja!

Die Tabelle interessentenundobjekte hat 2 Felder:

InteressentenNr   varchar
ObjektNr   varchar

Das heißt über diese Tabelle kann ich abfragen welcher Interessent welche Objekte besichtigt hat und natürlich andersherum wieviele Interessenten ein bestimmtes Objekt besichtigt haben.
Also eine m:n-Geschichte.

Kurzer Datenauszug:

InteressentenNr  ObjektNr
2005---0212  330
2005---0212  331
2005---0212  053
2005---0212  183
2005---0212  280
2005---0212  276
2005---0212  299
2005---0212  328
2005---0212  333
2005---0212  327
2005---0212  314
2005---0212  307
2005---0212  317
2005---0212  325
2005---0213  333
2005---0213  276
2005---0214  330
2005---0214  331
2005---0214  333
2005---0215  330
2005---0215  331
2005---0215  333

Grüße aus München

heinzflohe

[Ilja]

yo,

also wenn ich die semantik deiner abfrage richtig verstanden habe, willst du alle interessenten, die objekt 330 gesehen haben, aber nicht objekt 331. hoffe, ich habe das richtig verstanden.

SELECT InteressentenNr
FROM tabelle
WHERE ObjektNr IN (330, 331)
GROUP BY InteressentenNr
HAVING COUNT(DISTINCT ObjektNr) = 1

Ilja

[Ilja]

yo,

die abfrage ist ein wenig "bugy", weil sie auch datensätze mit nur 331 mit ins boot nimmt.

SELECT InteressentenNr
FROM tabelle
WHERE ObjektNr IN (330, 331)
GROUP BY InteressentenNr
HAVING Max(ObjektNr) = 330

das sollte besser sein.

Ilja

[heinzflohe]

Hi Ilja!
Genau das ist es.
Danke für Deine Hilfe.

Grüße aus München

heinzflohe

[dedlfix]

echo $begrüßung;

die abfrage ist ein wenig "bugy", weil sie auch datensätze mit nur 331 mit ins boot nimmt.

SELECT InteressentenNr
FROM tabelle
WHERE ObjektNr IN (330, 331)
GROUP BY InteressentenNr
HAVING Max(ObjektNr) = 330

das sollte besser sein.

Nicht wirklich. Jedenfalls ist die Abfrage jetzt auf die 330 festgelegt. Als Gegenprobe mal die 331 genommen ergeben sich wieder nicht die richtigen Werte. Natürlich könnte man nun min statt max nehmen, aber das ist dann auch wieder keine generelle Lösung.

Ich glaube nicht, dass die beiden Werte für die ObjektNr (330 und 331) feststehen sondern nur als Beispielwerte dienten.

echo "$verabschiedung $name";

[Ilja]

yo,

Ich glaube nicht, dass die beiden Werte für die ObjektNr (330 und 331) feststehen sondern nur als Beispielwerte dienten.

das sehe ich anders, es ist eine spezielle aufgabe und dafür wurde eine spezielle lösung gefunden. aber bitte, es geht auch allgemeiner, setzt aber vorraus, dass die spaltentypen zahlen sind ...

HAVING AVG(ObjektNr) = 330

damit kann man sogar beliebig viele Werte in der Liste mit aufnehmen und nicht nur zwei.

Ilja

[Ludger]

Hi,

HAVING AVG(ObjektNr) = 330

damit kann man sogar beliebig viele Werte in der Liste mit aufnehmen und nicht nur zwei.

da werd ich aber hellhoerig, bitte mal erlaeutern.

Gruss,
Ludger

[Ilja]

yo,

da werd ich aber hellhoerig, bitte mal erlaeutern.

was genau soll ich den erläutern, die ganze abfrage oder nur teile davon ?

Ilja

[Ludger]

Hi,

da werd ich aber hellhoerig, bitte mal erlaeutern.

was genau soll ich den erläutern, die ganze abfrage oder nur teile davon ?

nur die HAVING-Klausel.

Gruss,
Ludger

[Ilja]

yo,

nur die HAVING-Klausel.

die having klausel sorgt dafür, dass alle datensätze der jeweiligen gruppierung (in diesem fall ein interessent) einen durchschnittswert bilden. weicht dieser durchschnittswert von 330 ab, dann folgt daraus, dass noch andere werte für die objektid als 330 vorhanden sein müssen.

und damit kann man auch die Liste in der WHERE klausel vergrößern, zum beispiel zeige mit alle interessenten der objektid 330, die aber nicht 331, 332 oder 350 gesehen haben. ein wenig muss man aber dabei aufpassen, dass keine summmen in der liste 330 im durchschnitt bilden können, zum beispiel 60 und 600. diese beiden könnten im AVG auch wieder die zahl 330 erreichen. also ein wenig vorsicht ist auch noch hier geboten. aber für seine belange mit nur zwei werten in der liste reicht es allemal.

Ilja

[Ludger]

Hi,

ein wenig muss man aber dabei aufpassen, dass keine summmen in der liste 330 im durchschnitt bilden können, zum beispiel 60 und 600. diese beiden könnten im AVG auch wieder die zahl 330 erreichen.

OK.

also ein wenig vorsicht ist auch noch hier geboten. aber für seine belange mit nur zwei werten in der liste reicht es allemal.

Ich weiss manchaml nicht, ob Du in der "Hauptsache, es funzt"-Liga spielst oder im "Profi-Bereich".   ;-)

Gruss,
Ludger

[Ilja]

yo,

Ich weiss manchaml nicht, ob Du in der "Hauptsache, es funzt"-Liga spielst oder im "Profi-Bereich".   ;-)

ich kann nicht so richtig den unterschied finden. letztlich sollten auch die lösungen im profibereich "funzen" und nicht nur in der theorie gut aussehen. aber wenn es hilft kann man die abfrage noch wasserdichter machen.

was im endeffekt wichtig ist, dass man keine subselects verwendet und eine möglichkeit findet, alle datensätze eines interesssenten zusammenzuführen, um dann rückschlüße ziehen zu können. und das geht eben mit GROUP BY und HAVING, das eine vereint, dass andere selektiert. hier die wasserdichte version mit having

HAVING MAX(ObjektNr) = 330 AND MIN(ObjektNr) = 330

sieht halt auf den ersten blick ein wenig komisch aus, sollte es aber tun, egal welche und wieviele werte nun die liste besitzt.

Ilja

[dedlfix]

echo $begrüßung;

SELECT InteressentenNr FROM interessentenundobjekte WHERE InteressentenNr NOT IN (SELECT InteressentenNr FROM interessentenundobjekte WHERE ObjektNr = '331') AND ObjektNr = '330'

Mein Problem ist jetzt die Abfrage ohne Subselect zu schreiben

Nicht alle Subselect-Lösungen lassen sich ohne Subselect darstellen. Diese hier scheint mir so ein Fall zu sein.

Du möchtest die Interesenten für das Objekt 330 haben, davon aber die Interessenten des Objekts 331 ausschließen. Das heißt, dass du alle Interessenten für beide Objekte in einer Ergebnismenge haben musst, um dann irgendwie die "falschen" herauszufiltern. Die beiden Ergebnismengen dürfen sich zunächst nicht beeinflussen, was sie aber tun, wenn man sie miteinander verbindet. Vielleicht gelingt es mit ANDs und ORs und DISTINCT da was zurechtzufummeln, das erscheint mir aber dann genauso genial wie undurchsichtig zu werden.

Eine übersichtlichere Möglichkeit ist, die beiden Schritte nacheinander auszuführen. Die erste Ergebnismenge muss zwischengelagert werden.
Dafür bieten sich User Variablen an, die kennt MySQL auch schon eine Weile. Eine Uservariable kann aber nur einen Wert aufnehmen, keine Ergebnismenge. Man kann die Werte aber als String aneinanderfügen.
Nun wird noch eine Möglichkeit benötigt, diesen Ausdruck auszuwerten. MySQL kennt kein eval(), aber es kann mit für den Zweck ausreichenden regulären Ausdrücken was anfangen.

Zuerst wird nun der String mit den auszuschließenden Interessenten in einer Uservariable zusammengesetzt. In der Annahme, dass 0 kein gültiger Wert für InteressentenNr ist, wird zuerst die Uservariable grundinitialisiert

SET @var = '0';

Damit umgehen wir später das Entfernen eines überflüssigen Trennzeichens (|).

Die Werte der Interessenten für Objekt 331 werden jeweils mit | getrennt angehängt:

SELECT @var := CONCAT(@var, '|', InteressentenNr) FROM interessentenundobjekte WHERE ObjektNr = '331';

Ein SELECT @var; liefert jetzt etwas wie 0|115|220

@var wird nun in die Hauptabfrage integriert:

SELECT InteressentenNr FROM interessentenundobjekte WHERE InteressentenNr NOT RLIKE @var AND ObjektNr = '330';

Die drei Befehle müssen nacheinander und innerhalb einer Verbindung ausgeführt werden, da die UserVariablen verbindungsspezifisch sind und am Verbindungsende gelöscht wird.

echo "$verabschiedung $name";