Benötige Hilfe bei komplexer SQL Abfrage

[sunfy]

Hi Leutz,
Ich hänge im Moment an einer recht komplexen SQL Abfrage.
Kann mir da vielleicht jemand helfen?

Die Datenbank mit ihren Verknüpfungen hab ich mal als pdf ausgedruckt.
Datenbank.pdf

In meiner Abfrage benötige ich für jede Person die Daten KathX_Datum die mit den jeweiligen Personen verknüpft sind.
Pers_Name,
Pers_Vorname,
Max (Kath1_Datum)
Max(Kath1_Datum)
Max(Kath22_Datum)
Max(Kath3_Datum)
Max(Kath4_Datum)

Also Beispielsweise:

Delphi-Quellcode:

Person        Kath1       Kath2        Kath3         Kath4
Peter Müller  31.12.1996  12.05.2001   15.02.1999       -
Gustav Gans   12.05.1994       -       03.10.1996   01.02.2000

Sollte kein Datum für eine Kathegorie eingetragen sein, sollte das Feld leer bleiben.

Kann mir bei dieser Abfrage jem. behilflich sein.
Meine Versuche sind bisher gescheitert.
Und alle Tabellen mit allen Daten erstmal zu Joinen dauert zu lange.

Die Datenbank ist eine MsAccess Datenbank, die über eine ADOConnection ausgelesen wird.

Ist es da sinnvoller die Anfrage in Access zu schreiben und die Abfrage zu importieren, oder die Abfrage in eine Delphi Query zu schreiben?

Thnx Sunfy

[omata]

Moin,

habe mal schnell was zusammengeschrieben...

SQL-Code:

SELECT pers_name,
       pers_vorname,
       kath1.datum AS kath1,
       kath2.datum AS kath2,
       kath3.datum AS kath3,
       kath4.datum AS kath4
FROM person pe
LEFT JOIN (SELECT pers_id, MAX(kath1_datum) AS datum
           FROM pers_b_kath1 p
           INNER JOIN kathegorie1 k
             ON p.kath1_id = k.kath1_id
           GROUP BY pers_id) kath1
  ON pe.pers_id = kath1.pers_id
LEFT JOIN (SELECT pers_id, MAX(kath22_datum) AS datum
           FROM pers_b_kath2 p
           INNER JOIN kathegorie21 k1
             ON p.kath21_id = k1.kath21_id
           INNER JOIN kathegorie22 k2
             ON k1.kath22_id = k2.kath22_id
           GROUP BY pers_id) kath2
  ON pe.pers_id = kath2.pers_id
LEFT JOIN (SELECT pers_id, MAX(kath3_datum) AS datum
           FROM pers_b_kath3 p
           INNER JOIN kathegorie3 k
             ON p.kath3_id = k.kath3_id
           GROUP BY pers_id) kath3
  ON pe.pers_id = kath3.pers_id
LEFT JOIN (SELECT pers_id, MAX(kath4_datum) AS datum
           FROM pers_b_kath4 p
           INNER JOIN kathegorie4 k
             ON p.kath4_id = k.kath4_id
           GROUP BY pers_id) kath4
  ON pe.pers_id = kath4.pers_id

Vielleicht hilft dir das ja...

MfG
Thorsten

[jensw_2000]

Zitat:

Ist es da sinnvoller die Anfrage in Access zu schreiben und die Abfrage zu importieren, oder die Abfrage in eine Delphi Query zu schreiben?

Das spielt bei Access imho keine Rolle, weil bei Access ohnehin immer dein PC die komplette Arbeit übernimmt. Es gibt ja keinen SQL-Server auf den du den Job abwälzen kannst.

Zitat:

Und alle Tabellen mit allen Daten erstmal zu Joinen dauert zu lange.

Du wirst nicht daran vorbeikommen die Tabellen mit Joins zu verknüpfen.
Wenn du die Joins so gestaltest, das nur geringe Datenmengen zurückgegeben werden, sollte es nicht weig dauern

Beispiel:
Falls bei der SQL Syntax irgend etwas nicht 100% passt bitte ich um Entschuldigung. Meine Access Zeiten sind schön länger vorbei ...

SQL-Code:

SELECT       P.Pers_Vorname+' '+P.Pers_Name AS [Person]
            ,Max(K1.Kath1_Datum) AS [Kath1]
            ,Max(K2.Kath2_Datum) AS [Kath2]
            ,Max(K3.Kath3_Datum) AS [Kath3]
            ,Max(K4.Kath4_Datum) AS [Kath4]

FROM         Person P

-- erst die Bezugstabellen mit PERSON Inner Joinen ..
INNER JOIN 
  Pers_b_Kath1 PBK1
  ON  PBK1.Pers_ID = P.Pers_ID

INNER JOIN 
  Pers_b_Kath2 PBK2
  ON  PBK2.Pers_ID = P.Pers_ID

INNER JOIN 
  Pers_b_Kath3 PBK3
  ON  PBK3.Pers_ID = P.Pers_ID

INNER JOIN 
  Pers_b_Kath4 PBK4
  ON  PBK4.Pers_ID = P.Pers_ID

-- Dann die Kathegorie-Tabellen mit den Bezugstabellen Outer Joinen damit 
-- auch leere Kathegorie-Datensätze auftauchen..
LEFT OUTER JOIN
  Kathegorie1 K1
  ON K1.Kath1_ID = PBK1.Kath1_ID

LEFT OUTER JOIN
  Kathegorie2 K2
  ON K2.Kath2_ID = PBK2.Kath2_ID

LEFT OUTER JOIN
  Kathegorie3 K3
  ON K3.Kath3_ID = PBK3.Kath3_ID

LEFT OUTER JOIN
  Kathegorie4 K4
  ON K4.Kath4_ID = PBK4.Kath4_ID

-- Wenn möglich (unbedingt) mit der WHERE Klausel einen Filter auf die Tabelle Peron setzen
-- um die Result-Datenmenge möglichst klein zu halten
WHERE K.Name='Müller' OR K.Name='Meier'

-- alle "nicht in Aggregatfunktionen eingebundenen Felder" mit GROUP BY gruppieren
GROUP BY P.Pers_Vorname+' '+P.Pers_Name  -- ergänzt und korrigiert...

Ich hoffe das ist was Passenndes für dich.
Um die Perfornamce etwas zu steigern, solltest du auf jeden Fall die Felder "Pers_ID" und "KathX_ID" in allen relevanten Tabellen indizieren.


Schöne Grüße,
Jens

:hi:

[alzaimar]

Da fehlt am Ende noch ein 'GROUP BY P.Pers_Vorname+' '+P.Pers_Name'

[jensw_2000]

Zitat:

Da fehlt am Ende noch ein 'GROUP BY P.Pers_Vorname+' '+P.Pers_Name'

Stimmt war etwas spät gestern. Den Beitrag habe ich nun korrigiert ... Danke

[sunfy]

Danke schonmal für die schnellen Tipps
doch die Abfragen muß ich erst einmal verstehen.

Wenn ich sie einfach so eingebe kommt bei der Version von omata die Fehlermeldung

Delphi-Quellcode:

syntaxfehler (fehlender Operator) in Abfrageausdruck 'pe.pers_id=kath1.pers_id
LEFT JOIN (SELECT pers_id, MAX(Kath22_Datum) AS datum
  FROM pers_b_kath21 k1
  INNER JOIN Kathegorie21 k1
   ON p.kath21_id
  INNER JOIN kathegorie22 k2
 '

dachte erst, dass es an der fehlenden Klammerung des Select Argumentes liegt.
hab das Ganze dann geklammert und es kam die Meldung

Delphi-Quellcode:

syntaxfehler (fehlender Operator) in Abfrageausdruck '(Pers_Name,
 Pers_Vorname
 Pers_Vorame, 
 Kath1.datum AS Kath1, 
 Kath2.datum AS Kath2, 
 Kath3.datum AS Kath3, 
 Kath4.datum AS Kath4)'

Bei der Variante von jensw_2000 kommt auch die Fehlermeldung:
syntaxfehler (fehlender Operator) in Abfrageausdruck


Was genau bedeutet diese Fehlermeldung und wozu ist das 'AS' im select

thnx Sunfy

[Flocke]

Access hat eine ganz komische Art wie joins geklammert werden müssen, ich glaube das war so:

SQL-Code:

select * from a inner join b on ...
select * from (a inner join b on ...) left outer join c on ...
select * from ((a inner join b on ...) left outer join c on ...) inner join d on ...

... undsoweiter (bin mir aber nicht sicher).

Mache mal eine Abfrage mit mehreren joins mit dem visuellen Tool von Access und schau dir dann den SQL-Text an.

[jensw_2000]

Wenn du eine Aggregatfunktion verwendest z.B. SUM(Feld1),MAX(Feld2) dann hast du im Abfrageergebnis nicht mehr die Feldnamen von Feld1 und Feld2 stehen sondern Ausdruck1 und Ausdruck2 (bzw. Expr1 und Expr2). Die Feldnamen gehen auch "verloren" wenn man "berechnete Felder" erstellt (P.Pers_Vorname+' '+P.Pers_Name)

Die Anfrage würde also folgende Result-Datenmenge zurückgeben

Code:

Ausdruck1     Ausdruck2   Ausdruck3    Ausdruck4    Ausdruck5 
Peter Müller  31.12.1996  12.05.2001   15.02.1999       - 
Gustav Gans   12.05.1994       -       03.10.1996   01.02.2000

Damit wir also wieder sinnvolle Spaltennamen haben weisen wir diese per "AS [neuerSpltenname]" neu zu.

Zitat:

syntaxfehler (fehlender Operator) in Abfrageausdruck

Hast du den Code 1:1 übernommen ?

Hier ist ein ' zu viel drin

SQL-Code:

GROUP BY P.Pers_Vorname+' '+P.Pers_Name'

So soll es assehen

SQL-Code:

GROUP BY P.Pers_Vorname+' '+P.Pers_Name

Falls Access eine besondere Klammersetzung für Joins haben möchte kann ich auch nicht wirklich helfen...
Das ist zu lange her.

Alternativ kannst du die DB auch mal mit ein paar Demodatensätzen füttern, packen und mir per PN senden. Ich teste das heute Abend gern mal durch ... Dabei wird man nicht dümmer :coder:

:hi:

[sunfy]

Hatte die SQL Statements nicht genau so übernommen.
Brauche den Vornamen und Nachnamen auch nicht als ein String, sondern durchaus getrennt.
Daher ist die Vereinigung der beiden unnötig.

Werde jetzt ersteinmal ausprobieren, wie die Klammerung sitzen muß.

schonmal Danke für die Hilfe habt mir schon sehr geholfen

Gruß Sunfy

[omata]

Moin,

habe mich auch nochmal dran versucht...

SQL-Code:

SELECT person.nachname, 
       Max(Kath1.datum) AS Kath1, 
       Max(Kath22.datum) AS Kath2, 
       Max(Kath3.datum) AS Kath3, 
       Max(Kath4.datum) AS Kath4
FROM (((person 
        LEFT JOIN (pers_kath1 LEFT JOIN Kath1 ON pers_kath1.kath1_id = Kath1.kath1_id) 
          ON person.id = pers_kath1.pers_id) 
        LEFT JOIN (pers_kath3 LEFT JOIN Kath3 ON pers_kath3.kath3_id = Kath3.kath3_id) 
          ON person.id = pers_kath3.pers_id) 
        LEFT JOIN (pers_kath4 LEFT JOIN Kath4 ON pers_kath4.kath4_id = Kath4.kath4_id) 
          ON person.id = pers_kath4.pers_id) 
        LEFT JOIN (pers_kath21 LEFT JOIN (Kath21 LEFT JOIN Kath22 
          ON Kath21.Kath22_id = Kath22.Kath22_id)  
          ON pers_kath21.kath21_id = Kath21.kath21_id) 
          ON person.id = pers_kath21.pers_id 
GROUP BY person.nachname;

MfG
Thorsten

[sunfy]

@Omata
Deine Version funktioniert in leicht geänderter Weise.
Teilweise waren die Tabellennamen anders.

Mit dieser Anweisung funktioniert die Abfrage richtig, dauert aber ziemlich lange.
Außerdem werde ich per Eingabeaufforderung nach Person.Name, Person.Vorname und Person.ID gefragt.
Aber ich möchte doch alle Personen ausgeben lassen.

Delphi-Quellcode:

SELECT Person.Name,
       Person.Vorname,
       Max(Kathegorie1.Kath1_datum) AS [Kath1], 
       Max(Kathegorie22.Kath22_datum) AS [Kath2], 
       Max(Kathegorie3.Kath3_datum) AS [Kath3], 
       Max(Kathegorie4.Kath4_datum) AS [Kath4] 
FROM (((person 
        LEFT JOIN (pers_b_kath1 LEFT JOIN Kathegorie1 ON pers_b_kath1.kath1_id = Kathegorie1.kath1_id) 
          ON Person.id = pers_b_kath1.pers_id) 
        LEFT JOIN (pers_b_kath3 LEFT JOIN Kathegorie3 ON pers_b_kath3.kath3_id = Kathegorie3.kath3_id) 
          ON person.id = pers_b_kath3.pers_id) 
        LEFT JOIN (pers_b_kath4 LEFT JOIN Kathegorie4 ON pers_b_kath4.kath4_id = Kathegorie4.kath4_id) 
          ON person.id = pers_b_kath4.pers_id) 
        LEFT JOIN (pers_b_kath2 LEFT JOIN (Kathegorie21 LEFT JOIN Kathegorie22 
          ON Kathegorie21.Kath22_id = Kathegorie22.Kath22_id)  
          ON pers_b_kath2.kath21_id = Kathegorie21.kath21_id) 
          ON person.id = pers_b_kath2.pers_id 
GROUP BY Person.Name, Person.Vorname;

Ausgabe:

Delphi-Quellcode:

Name  Vorname     Kath1      Kath2       Kath3      Kath4 
Müller Peter    01.01.2005  02.02.2005             04.04.2005

Da die Abfrage zu lange dauerte, habe ich Person.id in Person.pers_id umbenannt, da die ID in der Tabelle Person Pers_ID heißt.
Jetzt geht die Abfrage richtig schnell, werde nur wieder nach Person.Name, Person.Vorname gefragt.
Allerdings gibt er bei einem leeren Feld irgendetwas aus.
Bei einem Feld, indem das Datum leer sein sollte, steht das MAX(Datum) der gesammten Tabelle Kathegorie3 drin, selbst, wenn dieses Datum gar nicht mit der Person verknüpft ist.

Delphi-Quellcode:

SELECT Person.Name,
       Person.Vorname,
       Max(Kathegorie1.Kath1_datum) AS [Kath1], 
       Max(Kathegorie22.Kath22_datum) AS [Kath2], 
       Max(Kathegorie3.Kath3_datum) AS [Kath3], 
       Max(Kathegorie4.Kath4_datum) AS [Kath4] 
FROM (((person 
        LEFT JOIN (pers_b_kath1 LEFT JOIN Kathegorie1 ON pers_b_kath1.kath1_id = Kathegorie1.kath1_id) 
          ON Person.Pers_id = pers_b_kath1.pers_id) 
        LEFT JOIN (pers_b_kath3 LEFT JOIN Kathegorie3 ON pers_b_kath3.kath3_id = Kathegorie3.kath3_id) 
          ON person.pers_id = pers_b_kath3.pers_id) 
        LEFT JOIN (pers_b_kath4 LEFT JOIN Kathegorie4 ON pers_b_kath4.kath4_id = Kathegorie4.kath4_id) 
          ON person.pers_id = pers_b_kath4.pers_id) 
        LEFT JOIN (pers_b_kath2 LEFT JOIN (Kathegorie21 LEFT JOIN Kathegorie22 
          ON Kathegorie21.Kath22_id = Kathegorie22.Kath22_id)  
          ON pers_b_kath2.kath21_id = Kathegorie21.kath21_id) 
          ON person.pers_id = pers_b_kath2.pers_id 
GROUP BY Person.Name, Person.Vorname;

Ausgabe:

Delphi-Quellcode:

Name  Vorname     Kath1      Kath2       Kath3      Kath4 
Müller Peter    01.01.2005  02.02.2005  23.04.1994  04.04.2005

Kathegorie3 müßte aber leer sein.
(23.04.1994 ist das größte Datum in Kathegorie3.Kath3_Datum)

Ist da vielleicht eine Klammer falsch gesetzt oder eine Variable falsch benannt?

Gruß Sunfy

[omata]

ich habe mir einfach mal deine Datenbank angelegt, um besser testen zu können. Die Namen habe ich aber nicht zu 100% übernommen - sorry.

Er fragt dich nach Name und Vorname, weil es diese Spalten nicht gibt. Aus deinen Beziehungen ist ersichtlich, dass diese Spalten Pers_Name und Pers_Vorname heissen. Oder sehe ich da jetzt was falsch?

MfG
Thorsten

[sunfy]

ups, da hast du Recht.
Kennst meine Datenbank schon besser als ich =)

Supi, jetzt funktioniert es schon fast so wie ich es zum Schluß benötige.
Auch die Zeit ist kein Problem mehr.

Jetzt muß ich nur noch zusehen, dass nur die Datum-Datensätze einbezogen werden, bei denen das jeweilige KathegorieX.KathX_Wertung = true ist.

Gruß Sunfy