Abfrage übe r2 Tabellen, zuviele Treffer

[Thomas]

Servus,

mich bringt gerade meine Datenbankabfrage zum Wahnsinn. Ich habe zwei Tabellen, in der zweiten sind die plz mehrfach vorhanden. In einer zweiten Spalte ist das Land (das immer das gleiche ist). Wenn ich nur eine Abfrage aus meiner ersten Tabelle mache, will ich mittels left JOIN aus meiner 2. Tabelle das Land erfahren. Nur wird das eine endlos Ausgabe. Wie bekomme ich es gebacken, wenn er den ersten Treffer erhält, das er zur nächsten Abfrage übergeht.

Thomas

table1
plz	ort
8200	München
4100	Hamburg

table2
plz	land
8200	Deutschland
8200	Deutschland 
8200	Deutschland


SELECT a.plz, a.ort, b.land FROM  table1 a 
left JOIN table2 b ON a.plz=b.plz

[dedlfix]

Tach!

In einer zweiten Spalte ist das Land (das immer das gleiche ist). Wenn ich nur eine Abfrage aus meiner ersten Tabelle mache, will ich mittels left JOIN aus meiner 2. Tabelle das Land erfahren. Nur wird das eine endlos Ausgabe.

Ja, das erzeugt ein kartesiches Produkt aus den beiden Mengen.

Wie bekomme ich es gebacken, wenn er den ersten Treffer erhält, das er zur nächsten Abfrage übergeht.

SQL basiert auf Mengen. Wenn du eine Abfrage erstellst wird eine Menge an Datensätzen geliefert. (Menge im Sinne der Mengenlehre, nicht als Synonym für Anzahl). Welche Datensätze darin enthalten sind bestimmt dein Einschränkungskriterium.

Man kann bei SQL-Abfragen kein Kriterium formulieren, um die Ermittlung einer Menge vorzeitig abzubrechen. Es gibt lediglich am Ende die Möglichkeit einer Einschränkung über LIMIT. Insbesondere kann LIMIT nicht als Verknüpfungskriterium bei Joins oder als eines der primären Einschränkungskriterien verwendet werden (sprich: bei WHERE). Das heißt, man muss die Teilmengen erst einzuschränken, bevor man sie verknüpft.

Um deinem Ziel näherzukommen musst du ein Kriterium finden, das eindeutig genug formuliert werden kann, damit lediglich ein einzelner Datensatz in der Ergebnismenge landet. So etwas wie FIRST() kann es nicht geben, weil beim WHERE die Datenmenge noch nicht sortiert ist, und deshalb keine Aussage über eine Reihenfolge getroffen werden kann.

Ich habe zwei Tabellen, in der zweiten sind die plz mehrfach vorhanden.

Die Frage dabei ist: Warum ist das so? Und gibt es eine Regel, mit der man ermitteln kann, welcher der vielen (als unsortiert zu betrachtenden) Datensätze mit derselben PLZ in der Ergebnismenge auftauchen soll? Solche Regeln müssen so formuliert werden können, dass dabei lediglich auf einen einzelnen Datensatz geschaut wird, also ohne dabei auf die anderen Datensätze zu schauen.

Es gibt verschiedene Ansätze, wie man an das Problem herangehen kann. Man kann versuchen, ohne die erste Tabelle zu betrachten, eine Abfrage für die zweite Tabelle so zu formulieren, dass lediglich ein Datensatz pro PLZ in der Ergebnismenge landet. Dann kann man diese Abfrage als Subquery verwenden, die mit Tabelle 1 verknüpft werden kann.

Eine andere Möglichkeit nennt sich Correlated Subquery, aber die kann man nur nehmen, wenn von Tabelle 2 nur ein einzelnes Feld benötigt wird (oder auch ein Ausdruck der einen einzelnen Wert liefert). Man formuliert dazu zuerst die Abfrage auf Tabelle 1 und separat eine zweite Abfrage auf genau diesen einen Wert aus Tabelle 2 mit Einschränkung auf die PLZ, in deinem Fall. Diese zweite Query kann man auch mit LIMIT auf eine Zeile eindampfen. Abschließend fügt man diese zweite Query als Subquery in der SELECT-Klausel der ersten Abfrage ein und setzt die Einschränkung auf die PLZ entsprechend dem Feld der ersten Tabelle. Zu Correlated Subquerys gibt es Kapitel im MySQL-Handbuch.

dedlfix.

[Rolf B]

Hallo dedlfix,

Die Frage dabei ist: Warum ist das so?

Das ist wohl der entscheidende Punkt. Ist "table2" die richtige Datenquelle für das Land? Denn wenn die PLZ dort mehrfach vorhanden sind, dann hat "table2" offenbar einen anderen Zweck.

Vor allem ist table2 dann nicht sauber normalisiert.

• Wenn plz ein Schlüsselattribut von table2 ist, dann gibt es offenbar weitere Schlüsselattribute, denn sonst könnte es nicht mehrere Rows zu einer PLZ geben. In diesme Fall ist das Land abhängig von einem Schlüsselteil -> 2NF verletzt.
• Wenn plz kein Schlüsselattribut von table2 ist, dann existiert eine Abhängigkeit zwischen Nichtschlüsselattributen -> 3NF verletzt.

Und die Folge: SQL Queries machen Kopfschmerzen.

Ein Workaround wäre eine DISTINCT Abfrage. Entweder im Join oder im Subselect

SELECT DISTINCT a.plz, a.ort, b.land
FROM  table1 a 
      LEFT JOIN table2 b ON a.plz=b.plz

SELECT a.plz, a.ort,
       SELECT DISTINCT land FROM table2 b WHERE a.plz = b.plz
FROM  table1 a 

Beides ist potenziell ineffizient, je nach DB-Inhalten. Die korrekte Lösung ist das Auslagern der (plz,land) Relation in eine eigene Table - was natürlich wieder andere Folgen hat, weil dann für jeden Zugriff auf den Ländernamen die neue Table befragt werden muss.

Rolf