SELECT JOIN MIN richtig kombinieren?

[Jürgen Thomas]

Hallo,

mit meinen vielen vergeblichen Versuchen komme ich immer stärker durcheinander und bitte deshalb um Hilfe.

• Tabelle ORTE2 enthält u.a. die Felder Ort-ID (= Alort) und Name (95.000 Datensätze).
• Tabelle PLZ enthält u.a. die Felder PLZ, Ort-ID, PLZ-Art, Str-Verz (40.000 Datensätze).
• Über Ort-ID ist kein ForeignKey möglich, weil es sich nicht um die eigentliche ORTE-Tabelle handelt, sondern um eine Tabelle mit nicht mehr gültigen Ortsnamen; aber Ort-ID soll als Verknüpfung dienen. Es bestehen folgende Verbindungen:
  1. Zu einem Eintrag aus ORTE2 gibt es 0 Einträge in PLZ.
  2. Zu einem Eintrag aus ORTE2 gibt es 1 Eintrag in PLZ.
  3. Zu einem Eintrag aus ORTE2 gibt es mehrere Einträge in PLZ.

Per View möchte ich eine Liste als Verbindung zwischen ORTE2 und PLZ haben: Wenn in PLZ kein Eintrag vorhanden ist, dann fehlt dieser Ort; bei einem Eintrag soll genau dieser für den JOIN verwendet werden; bei mehreren Einträgen soll derjenige mit der kleinsten PLZ (und nur dieser) verwendet werden.

Ziel soll etwas wie folgt werden:

SQL-Code:

SELECT ORTE2.Name, ORTE2.Alort,
       PLZ.PLZ,
       PLZ.Plz_Art,
       PLZ.Str-Verz
  FROM ORTE2
  JOIN PLZ ON PLZ.Alort = ORTE2.Alort

Es gelingt mir aber nicht, die JOIN-Klausel so zu erweitern oder ein SubSelect so einzubauen, dass MIN(PLZ) (oder FIRST 1) berücksichtigt wird.

Ergänzend muss ich darauf hinweisen, dass Firebird Probleme hat mit verschachtelten SubSelects oder IN-Prüfungen bei WHERE-Klauseln und sich gerne aufhängt. Nach Erledigung dieses Problems kommen noch ein weiterer JOIN und eine WHERE-Klausel hinzu; aber die sind unproblematisch.

Könnt Ihr mir einen Weg nennen? Danke! Jürgen

[marabu]

Hallo Jürgen,

ich denke, dass du einfach ein MIN(PLZ.PLZ) mit in die Selektion aufnehmen und die passende GROUP BY Klausel ergänzen musst.

Grüße vom marabu

[Jürgen Thomas]

Das hatte ich auch gehofft:

SQL-Code:

SELECT ORTE2.Name, ORTE2.Alort,
       MIN(PLZ.PLZ),
       PLZ.Plz_Art,
       PLZ.Str-Verz
  FROM ORTE2
  JOIN PLZ ON PLZ.Alort = ORTE2.Alort
  GROUP BY PLZ.PLZ

Das liefert (getestet im SQL-Fenster von IBExpert):

Zitat:

Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

Ich gebe zu, dass ich mit MIN und GROUP BY/ORDER BY noch zu wenig Ahnung habe und deshalb völlig unsicher bin, was ich der GroupBy-Klausel hinzufügen muss. Eigentlich muss es doch darum gehen, das JOIN einzuschränken, oder?

Jürgen

[DeddyH]

Wenn Du Aggregatfunktionen (hier MIN) verwendest, musst Du nach allen Nicht-Aggregatfeldern gruppieren.

[Jürgen Thomas]

Zitat von DeddyH:

Wenn Du Aggregatfunktionen (hier MIN) verwendest, musst Du nach allen Nicht-Aggregatfeldern gruppieren.

Das hatte ich schon gelesen, aber nicht ausprobiert: Zum einen soll meine "echte" View 14 Felder liefern, und Gruppierung über 14 Felder ist doch ziemlich aufwändig. Zum anderen liefert das aber wirklich ein falsches Ergebnis:

SQL-Code:

SELECT ORTE2.Name, ORTE2.Alort,
       MIN(PLZ.PLZ),
       PLZ.Plz_Art,
       PLZ.Str-Verz
  FROM ORTE2
  JOIN PLZ ON PLZ.Alort = ORTE2.Alort
  GROUP BY PLZ.PLZ,
       /* ergänzt um: */
       ORTE2.Name, ORTE2.Alort, PLZ.Plz_Art, PLZ.Str-Verz

Zuerst wird offensichtlich das JOIN ausgeführt, und das führt zu doppelten Einträgen (alle PLZ zur gleichen Ort-ID/Alort). Erst danach kommt die Gruppierung mit MIN, und das hat natürlich keine "echten" Auswirkungen mehr.

Nochmal: Beim JOIN will ich nur einen Eintrag aus PLZ holen, nämlich die kleinste PLZ.

Jürgen

[DeddyH]

Nimm das PLZ.PLZ mal aus der Gruppierung heraus, das macht an der Stelle keinen Sinn.

[Jürgen Thomas]

Zitat von DeddyH:

Nimm das PLZ.PLZ mal aus der Gruppierung heraus, das macht an der Stelle keinen Sinn.

Stimmt, aber das ändert an meinem Hauptproblem nichts. Jürgen

[DeddyH]

Schau Dir mal dies hier an.

[Jürgen Thomas]

Danke, DeddyH, das war ein sehr hilfreicher Hinweis.

Ich habe das erst einmal so eingebaut:

SQL-Code:

SELECT ORTE2.Name, ORTE2.Alort,
       p1.PLZ,
       p1.Plz_Art,
       p1.Str-Verz
  FROM ORTE2, PLZ p1
  WHERE (p1.Alort = ORTE2.Alort)
    AND (p1.Plz = (SELECT MIN(PLZ) FROM PLZ p2 WHERE p2.Alort = p1.Alort))

Das Ergebnis sieht recht gut aus, aber so stimmt es vermutlich noch nicht: Im Ergebnis erhalte ich nur 41.000 Datensätze, aber nach meiner Schätzung dürfte von den 95.000 Einträgen in ORTE2 nur ein kleiner Teil herausfallen. Das werde ich morgen weiter untersuchen und mich dann wieder melden.

Danach muss die Abfrage noch optimiert werden (ist nicht auch dabei ein JOIN möglich?), auch wegen der Feststellungen des Analyse-Plans.

Schönes Wochenende erstmal! Jürgen

[omata]

Hallo Jürgen,

ich versuche mich jetzt auch mal dran...

SQL-Code:

SELECT o.name, o.alort,
       p.plz, p.plz_art, p.str_verz
FROM orte2 o
INNER JOIN (SELECT alort, plz_art, str_verz, MIN(plz) plz 
            FROM plz
            GROUP BY alort, plz_art, str_verz) p
  ON o.alort = p.alort

Da du leider nichts über die beiden Spalten (plz_art und str_verz) verraten hast, ist diese Abfrage vielleicht problematisch und fehlerhaft.

Deshalb gleich noch ein Versuch, der diese Problematik umgeht...

SQL-Code:

SELECT o.name, o.alort,
       p.plz, p.plz_art, p.str_verz
FROM orte2 o
INNER JOIN (SELECT alort, plz_art, str_verz, plz 
            FROM plz p
            WHERE plz = (SELECT MIN(plz)
                         FROM plz
                         WHERE alort = p.alort)) p
  ON o.alort = p.alort

Gruss
Thorsten