Access 97 SQL-Abfrage

17/02/2009 - 16:25 von christian.kirchhoff | Report spam
Hallo,

ich pflege ein Delphi-6-Projekt, welches mit Access-97-Datenbanken
arbeitet. Nun bin ich auf der Suche nach einer Abfrage, die aber - wie
ich befürchte - so nicht realisierbar sein wird.

Es gibt zwei Haupttabellen, nennen wir sie "Source" und "Dest". Sie
stehen in einer m:n-Beziehung zueinander. Die weiteren Felder von
"Source" sind nicht wichtig, "Dest" hat ein wichtiges neues Feld
"Typ". Eine dritte Tabelle realisiert die Beziehung.

Felder von Source:
Key: AutoWert
...weitere Felder

Felder von Dest:
Key: AutoWert
Typ: Long Integer
...weitere Felder

Felder von Rel:
Key: AutoWert
SourceKey: Long Integer
DestKey: Long Integer

Ein DBGrid in der Applikation soll gewisse Felder von Source zeigen,
es sollte aber auch sichtbar sein, welche Datensàtze aus Source mit
mindestens einem Datensatz aus Dest verknüpft sind. Alle Datensàtze in
Dest haben eine Relation via "Rel" zu mindestens einem Datensatz in
Source.

Die Abfrage dazu lautete:
SELECT DISTINCT Source.Key as [Key], Source.*, Rel.SourceKey FROM
[Source]
LEFT JOIN [Rel]
ON Rel.SourceKey = Source.Key

Rel.SourceKey ist entweder leer oder entspricht Source.Key. Dies
reichte um zu erkennen, dass ein Datensatz aus Source mit einem
Datensatz aus Dest verknüpft ist.

Nun gibt es das erwàhnte neue Feld "Typ" in Tabelle "Dest". Ich möchte
nun weiterhin in dem DBGrid die Tabelle "Source" anzeigen, allerdings
möchte ich irgendwie erkennen, ob und mit welche Dest-Typen ein
Datensatz aus Source verknüpft ist. Eine Abfrage auch des Feldes
Dest.Typ über ein weiters JOIN, und die zusàtzliche Gruppierung mit
Verwendung von Aggregatfunktionen halte ich für nicht realistisch bzw.
hatte ich bei Tests immer wieder Fehlermeldungen:
- Wildcards (Source.*) sind in der Feldliste nicht erlaubt
- ich muss für jedes Feld eine Aggregatfunktion angeben, sonst gibt es
Fehlermeldungen
- wenn ich dann nach einem dieser Felder sortiere, gibt es wieder
einen Fehler, weil das scheinbar nicht erlaubt ist.
Zudem erlaubt Access 97 nur wenige Aggregatfunktionen, ich hàtte nur
SUM() verwenden können, die erlaubten Werte für Typ hàtten dann
Zweierpotenzen sein müssen, damit ich aus der Summe die einzelnen
Typen herausziehen kann.

Also erwàge ich nun, die Ansicht des DBGrids zu optionalisieren, also
zeige
- dass ein Datensatz aus Source mit keinem oder mit irgendeinem
Datensatz aus Dest verknüpft ist (dazu würde die obige Abfrage
reichen), oder
- dass ein Datensatz mit keinem oder mindestens einem Datensatz des
Dest.Typs 1 verknüpft ist, oder
- dass ein Datensatz mit keinem oder mindestens einem Datensatz des
Dest.Typs 2 verknüpft ist

Für die letzten beiden Optionen fehlt mit ein gültiger SQL-Ausdruck.
Bei einer Abfrage á la
SELECT DISTINCT Source.Key as [Key], Source.*, Rel.SourceKey FROM
([Source]
LEFT JOIN [Rel]
ON Rel.SourceKey = Source.Key)
LEFT JOIN [Dest]
ON (Rel.DestKey = Dest.Key AND Dest.Typ=1)
werden durch das erste JOIN bereits alle Source/Dest-Relationen aus
"Rel" erfasst, unabhàngig von Dest.Typ.

Es müsste erst einmal Rel reduziert werden auf die Datensàtze, die
einen Datensatz aus Dest des gewünschten Typs referenzieren, und das
Ergebnis müsste dann mit Source geJOINed werden.

Ich habe es versucht - erstmal ohne überhaupt nach Typ zu filtern -
mit:
SELECT DISTINCT Source.Key as [Key], Source.*, Rel.SourceKey FROM
([Dest]
LEFT JOIN [Rel]
ON Rel.DestKey = Dest.Key)
RIGHT JOIN [Source]
ON Rel.SourceKey = Source.Key

und

SELECT DISTINCT Source.Key as [Key], Source.*, Rel.SourceKey FROM
[Source]
LEFT JOIN ([Rel]
RIGHT JOIN [Dest]
ON Rel.DestKey = Dest.Key)
ON Rel.SourceKey = Source.Key

doch Delphi meldet jedesmal einen "Fehler im Abfrageausdruck".

Kann mir jemand sagen, wie die Abfrage formuliert werden müsste bzw.
ob solch eine Abfrage möglich ist?

Viele Grüße,

Christian Kirchhoff
 

Lesen sie die antworten

#1 lothar.armbruester
17/02/2009 - 22:09 | Warnen spam
writes:

Hallo,



[...]

Die Abfrage dazu lautete:
SELECT DISTINCT Source.Key as [Key], Source.*, Rel.SourceKey FROM
[Source]
LEFT JOIN [Rel]
ON Rel.SourceKey = Source.Key

Rel.SourceKey ist entweder leer oder entspricht Source.Key. Dies
reichte um zu erkennen, dass ein Datensatz aus Source mit einem
Datensatz aus Dest verknüpft ist.

Nun gibt es das erwàhnte neue Feld "Typ" in Tabelle "Dest". Ich möchte
nun weiterhin in dem DBGrid die Tabelle "Source" anzeigen, allerdings
möchte ich irgendwie erkennen, ob und mit welche Dest-Typen ein
Datensatz aus Source verknüpft ist. Eine Abfrage auch des Feldes
Dest.Typ über ein weiters JOIN, und die zusàtzliche Gruppierung mit
Verwendung von Aggregatfunktionen halte ich für nicht realistisch bzw.
hatte ich bei Tests immer wieder Fehlermeldungen:
- Wildcards (Source.*) sind in der Feldliste nicht erlaubt
- ich muss für jedes Feld eine Aggregatfunktion angeben, sonst gibt es
Fehlermeldungen
- wenn ich dann nach einem dieser Felder sortiere, gibt es wieder
einen Fehler, weil das scheinbar nicht erlaubt ist.
Zudem erlaubt Access 97 nur wenige Aggregatfunktionen, ich hàtte nur
SUM() verwenden können, die erlaubten Werte für Typ hàtten dann
Zweierpotenzen sein müssen, damit ich aus der Summe die einzelnen
Typen herausziehen kann.




Hallo Christian,
mir stellt sich die Frage, was Du angezeigt haben möchtest, wenn ein Satz aus
Source mehreren Sàtzen aus Dest zugeordnet ist, die auch noch unterschiedliche
Typen haben. Wenn Du mit einer Mehrfachanzeige der Sàtze aus Source leben
kannst, sollte eine Abfrage wie

SELECT DISTINCT Source.Key as [Key], Source.*, Dest.Typ FROM
([Source]
LEFT JOIN [Rel]
ON Rel.SourceKey = Source.Key)
LEFT JOIN [Dest]
ON (Rel.DestKey = Dest.Key)

das gewünschte Ergebnis liefern. Zu jedem Satz aus Source siehst Du damit alle
Typen aus Dest, die damit verbunden sind.

Ansonsten könnte ich mir noch etwas wie

SELECT Source.Key as [Key], <alle Felder aus Source>
min(Dest.Typ), max(dest.typ)
FROM
([Source]
LEFT JOIN [Rel]
ON Rel.SourceKey = Source.Key)
LEFT JOIN [Dest]
ON (Rel.DestKey = Dest.Key)
group by
<alle Felder aus Source>

vorstellen. Damit siehst Du zumindest den kleinsten und größten Typ zu jedem
Satz aus Source. Bei richtigen Datenbanken könnte man noch ein
count(distinct dest.typ) einfügen, um die Anzahl der verschiedenen Typen zu
erhalten. Das geht aber, soweit ich weiß, nicht mit Access.

Wenn Du zu jedem Satz aus Source alle damit verbundenen Typen, z.B. durch
komma getrennt, haben möchtest, fallen mir nur persistente Felder ein.
Im OnCalcFields holst Du dann per separatem SQL alle Typen zum Source.Key.
Das verspricht aber nicht wirklich performant zu werden.

Also erwàge ich nun, die Ansicht des DBGrids zu optionalisieren, also
zeige
- dass ein Datensatz aus Source mit keinem oder mit irgendeinem
Datensatz aus Dest verknüpft ist (dazu würde die obige Abfrage
reichen), oder
- dass ein Datensatz mit keinem oder mindestens einem Datensatz des
Dest.Typs 1 verknüpft ist, oder
- dass ein Datensatz mit keinem oder mindestens einem Datensatz des
Dest.Typs 2 verknüpft ist



Was ist, wenn ein Datensatz aus Source mit 3 Sàtzen aus Dest vom Typ 1, einem
Satz vom Typ 2 und 2 Sàtzen vom Typ 3 verknüpft ist? Wie soll denn in einem
solchen Fall die Ausgabe aussehen? Darf so ein Fall überhaupt auftreten?
Wenn so ein Fall möglich ist, kommst Du nur um eine Mehrfachanzeige der
Source-Sàtze herum, wenn Du eine Funktion hast, die die verschiedenen Werte
kommasepariert aufsammelt.

[...]

Viele Grüße,

Christian Kirchhoff



Wenn die verschiedenen Typen in Dest nicht zu viele sind und sich insbesondere
nicht vermehren, könnte man auch soetwas machen. Das ist jetzt ungetestet und
Oracle-Syntax, die evtl. für Access angepasst werden muss oder gar nicht geht:

select distinct
source.key,
<alle felder aus source>,
sum(case when dest.typ=1 then 1 else 0 end) as anz_1,
sum(case when dest.typ=2 then 1 else 0 end) as anz_2,
sum(case when dest.typ=3 then 1 else 0 end) as anz_3
from
(source left join rel on source.key=rel.sourcekey)
left join dest on dest.key=rel.destkey
group by
source.key,
<alle felder aus source>

Ggf. muss man das case durch iif o.à. ersetzen.

Evtl. kann man auch folgendes machen:

select distinct
source.key,
source.*,
d.anz_1,
d.anz_2,
d.anz_3
from
source left join
(select
rel.sourcekey,
sum(case when dest.typ=1 then 1 else 0 end) as anz_1,
sum(case when dest.typ=2 then 1 else 0 end) as anz_2,
sum(case when dest.typ=3 then 1 else 0 end) as anz_3
from
rel inner join dest on rel.destkey=dest.key
group by
rel.sourcekey) d
on source.key=d.sourcekey

HTH,
Lothar

Lothar Armbrüster |
Hauptstr. 26 |
65346 Eltville |

Ähnliche fragen