SQL SELECT - steh auf dem Schlauch

[SQLSelect]

Hallo,

ich stehe gerade voll auf dem Schlauch. Ich muss eine Abfrage erstellen, bei dem nur Kunden aufgelistet werden, die NUR den Status 9 haben dürfen. Sobald der Kunde eine Aktivität mit 0,1,2 oder 3 hat darf er nicht angezeigt werden. Ich bekomme es aber nicht hin.

Hier meine SQL Abfrage:

SELECT ak.KontaktNummer, an.Name, an.Strasse, an.Postleitzahl, an.Ort
FROM Vertragskoepfe vk

INNER JOIN Aktivitaeten ak
ON ak.KontaktNummer = vk.Kundennummer

INNER JOIN AktivitaetenMitarbeiter am
ON am.LfdNr = ak.LfdNr

RIGHT JOIN Kontakte k
ON k.Kontaktnummer = ak.KontaktNummer

INNER JOIN Anschriften an
ON an.AnschriftsNummer = k.HauptAnschrift

WHERE vk.Vertragsende > GETDATE() AND am.Bearbeitungsstatus = '9'

GROUP BY ak.KontaktNummer, an.Name, an.Strasse, an.Postleitzahl, an.Ort

Das Problem ist nun, dass der Kunde evtl. ja Status 9 und einer der anderen besitzt. Dann wird der auch angezeigt. Was nicht sein darf. Geht sicher irgendwie mit LEFT JOIN oder so, ich bekomme es einfach nciht hin. Kann mir da jemand helfen?

Danke

[Rolf b]

Uff, ohne Datenmodell ist erstmal Reengineering fällig :)

Also - deine Ausgabe besteht aus Kontaktadressen (wobei ich das merkwürdig finde, dass der Name an der Adresse steht und nicht am Kontakt, aber na gut, das mag Gründe haben...). Deine primäre Query ist also

SELECT k.KontaktNummer, an.Name, an.Strasse, an.Postleitzahl, an.Ort
FROM Kontakte k INNER JOIN Anschriften an
                ON an.AnschriftsNummer = k.HauptAnschrift

und das sollte auch oben stehen. Die Kontaktnummer sollte aus der Kontakte-Tabelle gelesen werden, weil sie da der Primärschlüssel sein dürfte. Der Lesbarkeit halber.

Diese Abfrage wird nun eingegrenzt. Erstmal auf gültige Verträge, aber diese bitte als direkte Relation und nicht als Umweg über die Aktivitäten. Wieder aus Lesbarkeitsgründen.

SELECT k.KontaktNummer, an.Name, an.Strasse, an.Postleitzahl, an.Ort
FROM Kontakte k 
     INNER JOIN Anschriften an ON an.AnschriftsNummer = k.HauptAnschrift
     INNER JOIN Vertragskoepfe vk ON vk.Kundennummer = k.KontaktNummer
WHERE vk.Vertragsende > GETDATE()

So. Und dass muss nun eingegrenzt werden auf die Fälle, wo es ausschließlich Aktivitäten gibt wo der AktivitätenMitarbeiter (eine m:n-Relation, ja?) den Bearbeitungsstatus 9 hat. Das macht man mit einer EXISTS Abfrage. Wir wollen nur die haben, die 9 sind - was sich durch doppelte Verneinung abfragen lässt: es darf keinen geben, der nicht 9 ist.

SELECT k.KontaktNummer, an.Name, an.Strasse, an.Postleitzahl, an.Ort
FROM Kontakte k 
     INNER JOIN Anschriften an ON an.AnschriftsNummer = k.HauptAnschrift
     INNER JOIN Vertragskoepfe vk ON vk.Kundennummer = k.KontaktNummer
WHERE vk.Vertragsende > GETDATE()
  AND NOT EXISTS (SELECT *
                  FROM Aktivitaeten ak JOIN AktivitaetenMitarbeiter am ON ak.LfdNr = am.LfdNr
                  WHERE ak.KontaktNummer = k.KontaktNummer 
                    AND am.Bearbeitungsstatus <> 9)

Das sollte so bis auf Flüchtigkeitsfehler (weil nicht getestet) stimmen.

Wozu Du den GROUP BY brauchst, weiß ich nicht, du hattest keine Aggregation in der SELECT Liste. Ich habe ihm darum weggelassen.

Rolf

[SQLSelect]

Hammer!!!!

Vielen Vielen Dank! Funktioniert bestens.

Ein paar Anmerkungen und Fragen ahbe ich dazu:

Das GROUP BY habe ich, da es einige Aktiviteneinträge mit 9 gibt, d.h. ohne GROUP wäre der gleiche Kunde mehrmals in der Abfrage vorhanden - mach ich doch dann richtig oder nicht?

Dein Hinweis mit der Abfrage auf gültige VErträge verstehe ich nicht. Was meinst Du mit "direkte Relation und nicht über Umwege..."? Ich habe doch die gültigen Vertrge direkt abgefragt oder was meinst Du damit?

Danke

[dedlfix]

Tach!

Das GROUP BY habe ich, da es einige Aktiviteneinträge mit 9 gibt, d.h. ohne GROUP wäre der gleiche Kunde mehrmals in der Abfrage vorhanden - mach ich doch dann richtig oder nicht?

Nein, GROUP BY als Antwort auf ein ungewolltes kartesisches Produkt kann nicht die Lösung sein, zumal das nur unter MySQL überhaupt geht. Wenn du gruppierst und du andere Daten als die gruppierten oder aggregierte (Funktionen wie SUM/COUNT) in die Select-Klausel stellst, von welchem Datensatz soll der Feldinhalt dann genommen werden? Das geht nur dann problemlos, wenn die Daten durch einen zu wenig einschränkenden Join vervielfältigt wurden oder sie redundant in der Tabelle stehen und die zufälle Auswahl seitens MySQL genau die gewünschten Daten bringt.

Wenn eine Tabelle zu viele Daten enthält, die man für einen Join nicht braucht, kann man zum Beispiel wie folgt vorgehen. Man erstellt erstmal eine Query für diese ansonsten gejointe Tabelle, die exakt die Datenmenge liefert, die man braucht. Darin kann man auch gruppieren, wenn man irgendeine Aggregatfunktion benötigt. Diese Query kann man dann als Subquery in der Hauptquery joinen. Falls sie nur ein einzelnes Feld als Ergebnis hat, kann man sie auch gleich in die Select-Klausel schreiben, in Form einer correlated subquery.

dedlfix.

[Rolf b]

GROUP BY: Wenn Du doppelte Zeilen entfernen willst, schreibst Du besser SELECT DISTINCT. GROUP BY führt natürlich auch dazu, dass mehrfach auftretende Zeilen entfernt werden, ist aber eigentlich dafür da, innerhalb einer Gruppe mehrfacher Zeilen Werte zu aggregieren (SUM, AVG, COUNT, etc). Ich wusste bisher gar nicht, dass man GROUP BY überhaupt angeben darf, ohne eine solche Aggregationsfunktion in der SELECT Liste zu haben.

Mit der EXISTS Abfrage hat sich das Problem aber ohnehin erledigt, damit führen mehrfache 9er Status nicht mehr zu mehrfachen Sätzen.

Gültige Verträge: Du hast es indirekt gemacht, erst von Kontakten zu Aktivitäten und dann von Aktivitäten zu Vertragsköpfen. Das funktioniert natürlich (wegen $$A=B\ \mathrm{und}\ B=C \Longrightarrow A=C$$), es ist aber verständlicher, die Dinge so direkt wie möglich zu formulieren. Du suchst Verträge zum Kontakt, nicht Verträge zu einer Aktivität, oder?

Rolf