Home > Artikel > Ausgabe 4/2016 > Unterabfragen als Feldinhalt

Unterabfragen als Feldinhalt

Achtung: Sie sind nicht angemeldet. Wenn Sie Abonnent sind und sich anmelden, lesen Sie den kompletten Artikel, laden das PDF herunter oder probieren die Beispieldatenbank aus (sofern vorhanden).

Dass gespeicherte Abfragen selbst wieder als Pseudotabellen in andere Abfragen integriert und dort mit anderen Tabellen verknüpft werden können, dürfte Ihnen bekannt sein. Weniger verbreitet jedoch sind jene Abfragen, die ungespeichert als SQL-Ausdruck zur Berechnung von Feldinhalten und Bedingungen dienen. Diese mit den Schlagwörtern Unterabfrage oder Subselect bezeichneten Ausdrücke sind Gegenstand dieses Beitrags.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1604_Unterabfragen.accdb

Unterabfragen, Subselects

Eine Unterabfrage (engl. Subselect) ist nichts weiter, als eine vollständige in SQL geschriebene Auswahlabfrage, deren Ergebnis oder Ergebnisse zur Anzeige eines einzelnen Felds oder zur Bestimmung eines Auswahlkriteriums in einer anderen Abfrage dienen. Im Folgenden verwenden wir einige Beispiele, um verschiedene Möglichkeiten im Umgang mit diesen Unterabfragen zu demonstrieren.

Grundlage für die Beispieldatenbank ist das Datenmodell in Bild 1, welches Kunden und deren Bestellungen verwaltet. Neben den Kundendaten, bestehend aus der Tabelle tblKunden und deren Nachschlagetabellen tblAnreden, tblOrte und tblLaender, sind die Bestellungen in der Tabelle tblBestellungen festgehalten und zu jeder Bestellposition jeweils ein Datensatz in der Tabelle tblBestelldetails. Die Artikel jeder Bestellposition kommen schließlich aus der Nachschlagetabelle tblArtikel.

Beziehungen zwischen Kunden und deren Bestellungen im Datenmodell der Beispieldatenbank

Bild 1: Beziehungen zwischen Kunden und deren Bestellungen im Datenmodell der Beispieldatenbank

Auswertungen über Subselects

Eine Anforderung an eine Auswertung könnte nun sein, die Anzahl von Bestellungen in einer Übersicht darzustellen, die jeder Kunde getätigt hatte. Die dafür geeignete Abfrage wäre eine Gruppierungs- beziehungsweise Aggregatabfrage. Bild 2 zeigt, wie Sie diese gestalten könnten. Aus der Kundentabelle entnehmen wir lediglich die ID der Datensätze und den vollständigen Namen Kunde, welcher sich aus Nachname und Vorname zusammensetzt:

Die Abfrage gibt nur jene Kunden aus, die mindestens einmal bestellten

Bild 2: Die Abfrage gibt nur jene Kunden aus, die mindestens einmal bestellten

...Nachname & ', ' & Vorname AS Kunde

Um die Anzahl von Bestellungen zu jedem Kunden zu ermitteln, wird die Tabelle tblBestellungen mit der Kundentabelle 1:1 verknüpft. Die Berechnung der Anzahl erfordert als Aggregat, dass Sie aus der Abfrage eine Gruppierungsabfrage machen, indem Sie das Summensymbol ? im Entwurfs-Ribbon anklicken. Für die nun eingeblendete Zeile Funktion geben Sie bei ID und Kunde die Gruppierung an, und beim Feld Anzahl Bestellungen die ID von tblBestellungen, sowie die Funktion Anzahl. Die Bedingung, dass diese Anzahl größer, als 0, sein muss, ist eigentlich überflüssig, denn durch die 1:1-Verknüpfung werden ohnehin alle Datensätze ausgefiltert, die keinen Bezug zwischen den Tabellen besitzen.

Im Ergebnis haben Sie nun alle Kunden, die überhaupt etwas bestellten und die Gesamtzahl ihrer Bestellungen. Sie könnten noch nach diesem Feld absteigend sortieren, um die Top-Besteller zu ermitteln.

Diese Auswertung lässt sich aber auch anders realisieren. Statt die Tabelle tblBestellungen direkt in den Abfrageentwurf zu ziehen, wird sie in einen SELECT-Ausdruck als Unterabfrage integriert, der das Ausgabefeld Anzahl Bestellungen speist. Wie das aussieht, zeigt Bild 3. Hier wird bereits deutlich, dass solche Abfragen in der Entwurfsansicht etwas schwieriger zu handeln sind, da die SQL-Ausdrücke für die Unterabfragen relativ lang ausfallen und damit die Spaltenbreite dieser berechneten Felder ziemlich breit aufgezogen werden müsste. Entweder Sie verwenden dann die Zoom-Funktion für das Feld (rechter Mausklick), oder Sie schalten besser gleich in die SQL-Ansicht, die den Code von Listing 1 zutage fördert. Das Ergebnisfeld Anzahl Bestellungen ist hier mit n bezeichnet. Es errechnet sich über die Aggregatfunktion COUNT(*) auf die Datensätze von tblBestellungen. Da wir ja aber nicht für jeden Kunden die Gesamtzahl aller Bestellungen ausgeben möchten, sondern nur die Zahl seiner Bestellungen, braucht es noch eine zusätzliche WHERE-Bedingung. Pro Kundendatensatz müssen die Bestellungen gemäß seiner ID gefiltert werden.

Auch hier zeigen sich nur Kunden mit Bestellungen, jedoch mit Unterabfrage

Bild 3: Auch hier zeigen sich nur Kunden mit Bestellungen, jedoch mit Unterabfrage

Hier kommt nun ein Trick ins Spiel: Die Tabelle tblBestellungen wird in den Alias-Namen TMP umbenannt. Auf ihr Feld KundeID können Sie somit über TMP.KundeID Bezug nehmen. Ihr Wert muss mit der ID der Tabelle tblKunden übereinstimmen, damit die Filterung greift. Also setzen Sie diese Bedingung ein:

...WHERE TMP.KundeID = tblKunden.ID

Das Ergebnis stimmt mit dem der Abfrage aus Bild 2 überein.

Man könnte meinen, dass die Performance dieser Abfrage darunter litte, dass für jeden Kundendatensatz die Unterabfrage zur Anzahlberechnung erneut ausgeführt werden muss. Tatsächlich unterscheidet sie sich nicht nennenswert von der gruppierten Abfrage oben, denn auch bei Aggregatabfragen muss intern für jeden Datensatz eine eigene neue Berechnung durchgeführt werden.

Nummerieren

Der prominenteste Vertreter von Unterabfragen ist die Lösung zum Durchnummerieren der ausgegebenen Datensätze. Sie möchten etwa alle Kunden der Datenbank ausgeben und die Datensätze fortlaufend nummerieren. Unter Excel wäre das ein leichtes Unterfangen. Sie fügten hier einfach ganz vorn eine zusätzliche Spalte ein, schrieben eine 1 und eine 2 in die beiden ersten Zellen hinein und zögen die Spalte nach Markierung dieser zwei Zellen über die ganze Kundentabelle auf.

Leider gestaltet sich das unter Access weitaus schwieriger. Eine Nummerierungsfunktion fehlt hier.

Über eine gewiefte Unterabfrage lässt sich die Nummerierung dennoch erreichen. Voraussetzung allerdings ist, dass die Tabelle einen Primärschlüssel, wie die Autowert-ID von tblKunden, aufweist. Ohne diesen wäre die Sache nur über eine gar nicht so einfache VBA-Funktion zu lösen.

Das Feld Pos in Bild 4 stellt das Nummernfeld dar. Es berechnet sich über eine Unterabfrage, die in Listing 2 besser zu überblicken ist. Zunächst ermittelt COUNT die Anzahl der Datensätze der Tabelle tblKunden. Hätte sie 9.000 Datensätze, so gäbe Pos in jeder Zeile eben diesen Wert aus, was nicht wünschenswert ist. Eine zusätzliche Bedingung ändert aber die Situation.

Durchnummerieren der Kunden über Unterabfrage

Bild 4: Durchnummerieren der Kunden über Unterabfrage

SELECT 1+(SELECT COUNT(*) FROM tblKunden AS TMP WHERE TMP.ID

   AS Pos,

tblKunden.* FROM tblKunden;

Listing 2: SQL-Code der Abfrage zum Durchnummerieren der Kunden

Auch hier wird die Tabelle intern über den Alias in TMP umbenannt. Anschließend filtert die WHERE-Bedingung diese TMP-Tabelle so, dass nur jene Datensätze berücksichtigt werden, deren ID (Autowert!) kleiner ist, als die aktuell im Datensatz stehende ID der angezeigten tblKunden:

...WHERE TMP.ID < tblKunden.ID

Also filtert die Abfrage zu jedem Kundendatensatz die virtuelle Kundentabelle (TMP) so, dass ihre Datensatzzahl der entspricht, die bis zu dieser Position erreicht wurde. Diese Anzahl wird in Pos als Nummerierung zweckentfremdet.

Eine absteigende Nummerierung erreichen Sie, indem Sie statt des Kleiner-Zeichens das Größer-Zeichen in die Bedingung einsetzen. Jede Tabelle mit einem Autowert lässt sich auf diese Weise durchnummerieren. Später zeigen wir noch ein Beispiel, wie sich die Artikel jeder Bestellung durchnummerieren lassen. Die Unterabfrage dazu fällt etwas umfangreicher aus, weil sie noch weitere Bedingungen enthalten muss.

Unterabfragen für Bedingungen

Eine Unterabfrage gibt einen oder mehrere Datensätze zurück. Soll ein Feldwert ermittelt werden, so darf die Zahl dieser Datensätze naturgemäß nicht größer sein, als 1. Schließlich kann eine Feldzelle ja nicht mehr anzeigen, als einen Wert. Die Aggregatfunktion COUNT etwa garantiert dies, ebenso, wie FIRST (Erster Wert) oder LAST (Letzter Wert) oder AVG (Mittelwert). Verwenden Sie hier Unterabfragen, die mehrere Datensätze zurückliefern, so wird Access bereits beim Abspeichern der Abfrage meckern und Sie darauf aufmerksam machen, dass eine solche Unterabfrage nur einen Wert zurückgeben darf.

Anders bei Unterabfragen, die für Bedingungen benutzt werden. Als Beispiel möchten wir alle Kunden ausgeben, die irgendwann Bestellungen tätigten. Das Kriterium dafür ist, dass ihre Kunden-ID in der Tabelle tblBestellungen irgendwo auftaucht. Die Abfrage in Bild 5 baut auf der früheren auf, in der die Gesamtzahl aller Bestellungen eines Kunden bestimmt wurde. Auch hier findet für diese Feldberechnung eine Unterabfrage Anwendung. Der Nachteil dieser Lösung besteht darin, dass alle Kunden ausgegeben werden, also auch jene, bei denen COUNT den Wert 0 zurückgibt. Natürlich könnte man als Kriterium für dieses Feld den Ausdruck >0 bestimmen. Die Abfrage müsste dann für jeden Kunden seine Bestellzahl ermitteln und aus dem Ergebnis all jene streichen, deren Wert 0 betrüge. Eine Vorfilterung macht diese Abfrage aber schneller.

Alle Kunden mit Bestellungen über IN-Statement auf tblBestellungen

Bild 5: Alle Kunden mit Bestellungen über IN-Statement auf tblBestellungen

Dazu wird im dritten Feld nachgeschaut, ob die Kunden-ID überhaupt in der Tabelle tblBestellungen vorkommt. Das IN-Statement ist dafür der geeignete SQL-Ausdruck. Sie finden ihn vollständig in der vierten Zeile von Listing 3. Nach dem Tag IN hat eine Auswahlliste in Klammern zu folgen. Hat die ID einen Wert, der mit einem der Elemente der IN-Klammer übereinstimmt, so ist der Rückgabewert der Funktion True. Die Auswahlliste kommt hier in Gestalt einer Unterabfrage daher:

SELECT [Nachname] & "," & [Vorname] AS Kunde,

(SELECT COUNT(*) FROM tblBestellungen As TMP WHERE (TMP.KundeID=tblKunden.ID)) AS n

FROM tblKunden

WHERE [tblKunden].[ID] In (SELECT KundeID FROM tblBestellungen);

Listing 3: Hier der SQL-Code von Abfrage3

SELECT KundeID FROM tblBestellungen

Eine Alternative zum IN-Statement ist die ANY-Klausel, die noch etwas schneller arbeitet. Dazu nochmals ein Beispiel, welches alle Kunden mit Bestellungen ermittelt. Die Abfrage in Bild 6 zeigt namentlich lediglich alle Kunden mit Bestellungen, wobei als Kriterium für die Kunden-ID hier das ANY-Statement herhält, dem die gleiche Unterabfrage in Klammern zugewiesen wird, wie im vorigen Beispiel.

Kunden mit Bestellungen, hier über das ANY-Statement ermittelt

Bild 6: Kunden mit Bestellungen, hier über das ANY-Statement ermittelt

Der Unterschied zum IN-Statement besteht darin, dass hier nicht eine Mengenfunktion vorliegt, sondern ein Wertvergleich, erkennbar am Gleichheitszeichen (siehe Listing 4). Intern sollte die Access Database Engine dabei wohl denselben Algorithmus verwenden, aus irgendeinem Grund ist das ANY-Statement jedoch etwas schneller. Möglicherweise cachet sie hier das Ergebnis der Unterabfrage. Microsoft schweigt sich über die Interna aus.

SELECT tblKunden.ID, [Nachname] & "," & [Vorname] AS Kunde

FROM tblKunden

WHERE tblKunden.ID=Any (SELECT KundeID FROM tblBestellungen);

Listing 4: Das ist der doch recht übersichtliche SQL-Code von Abfrage4

Natürlich kann man Unterabfragen auch noch beliebig weiter ausbauen. Die Abfrage in Listing 5 benutzt ebenfalls das IN-Statement, filtert die Auswahlmenge jedoch über das Limit TOP 5. Also gibt die Klammer, die Unterabfrage, immer nur fünf Datensätze zurück, wobei diese zusätzlich nach der ID des Kunden sortiert sind. Die Abfrage insgesamt zeigt damit ebenfalls nur fünf Kunden an, die außerdem noch nach Kundennamen sortiert sind.

SELECT tblKunden.ID, [Nachname] & "," & [Vorname] AS Kunde FROM tblKunden

WHERE tblKunden.ID In (SELECT TOP 5 KundeID FROM tblBestellungen ORDER BY KundeID DESC)

ORDER BY [Nachname] & "," & [Vorname];

Listing 5: Abfrage5 zeigt die ersten fünf Kunden mit Bestellungen über IN-Klausel

Hauptabfragen kürzen

Aus Webshops kennen Sie vielleicht die Funktion Kunden bestellten auch diese Artikel. Zur aktuell angezeigten Ware werden in einer Liste jene Artikel aufgeführt, die häufig ebenfalls in den Warenkorb wanderten. Auch unsere Beispieldatenbank kann mit diesem Feature aufwarten. Die Abfrage in Bild 7 zeigt, wie's geht.

Über diese Abfrage werden zu einem Artikel andere Artikel angezeigt, die mit ihm zusammen bestellt wurden

Bild 7: Über diese Abfrage werden zu einem Artikel andere Artikel angezeigt, die mit ihm zusammen bestellt wurden

Gefragt ist, welche anderen Artikel außer Gustaf's Knäckebröd parallel bestellt wurden. Also muss als Kriterium für den Artikelnamen der ersten Tabelle tblArtikel der String Gustaf's Knäckebröd stehen. Die 1:1-Verknüpfung mit tblBestelldetails gewährleistet, dass nur die Bestellpositionen im Ergebnis auftauchen, in denen der Artikel vorkam. Zu jeder Bestellposition gibt nun es eine zugehörige Bestellung, die durch das Feld BestellungID identifiziert ist. Damit weiß man, welche Bestellungen uns im Verein mit dem Knäckebröd weiter interessieren. Nun wird an die Tabelle eine weitere Instanz der tblBestelldetails (tblBestelldetails_1) geknüpft, wobei die Feldinhalte von BestellungID übereinstimmen müssen. Bis hierher haben wir im Ergebnis also alle Bestellungen und Bestellpositionen mit der gewünschten ID, wobei diese nun natürlich weitere Artikel aufweisen können. Deren Name wird über eine weitere Instanz der tblArtikel bestimmt und als Feld Andere Artikel zurückgegeben. Das sieht in der Datenblattansicht aus, wie in Bild 8.

Ergebnis der Abfrage Andere Artikel (Abfrage6)

Bild 8: Ergebnis der Abfrage Andere Artikel (Abfrage6)

Sie haben das Ende des frei verfügbaren Teil dieses Artikels erreicht!

Wenn Sie mehr lesen und auf viele weitere Artikel zugreifen möchten, melden Sie sich als Abonnent unter Login an. Falls nicht, bestellen Sie doch einfach ein Jahresabonnement!