SQL-Abfrage über vier Tabellen

Fragen zu allen anderen Programmiersprachen.
Andesdaf
Moderator
Beiträge: 2658
Registriert: 15.06.2008 18:22
Wohnort: Dresden

SQL-Abfrage über vier Tabellen

Beitrag von Andesdaf »

Hallo, ich habe folgende Tabellen in einer MS-Access-Datenbank:

Code: Alles auswählen

sportler
- id
- swid
- aktiv
- adresse (= adresse.id)

adresse
- id
- vorname
- name

meldung
- id
- sportler (= sportler.id)
- wettkampf (= wettkampf.id)

wettkampf
- id
- veranstaltung
Eine Veranstaltung besteht aus mehreren Wettkämpfen, zu denen Sportler gemeldet werden können. Ein Sportler kann mehrmals zu
unterschiedlichen Wettkämpfen, aber zu einem einzelnen Wettkampf nur einmal gemeldet werden.

Ich möchte grundsätzlich alle Sportler mit sportler.aktiv = 1 ausgeben. Es sollen Name, Vorname und swid (weitere Kennung) ausgegeben werden.
Zusätzlich habe ich eine wettkampf.veranstaltung gegeben. Ich möchte dann zu jedem Sportler die Anzahl der Meldungen zu dieser Veranstaltung
mit ausgeben, auch wenn diese 0 ist, es quasi kein Element in der Tabelle meldung gibt, das einen Zusammenhang zwischen sportler und wettkampf herstellt.

Hat jemand eine Idee, wie das realisierbar wäre?
Win11 x64 | PB 6.00 (x64)
Benutzeravatar
NicTheQuick
Ein Admin
Beiträge: 8675
Registriert: 29.08.2004 20:20
Computerausstattung: Ryzen 7 5800X, 32 GB DDR4-3200
Ubuntu 22.04.3 LTS
GeForce RTX 3080 Ti
Wohnort: Saarbrücken
Kontaktdaten:

Re: SQL-Abfrage über vier Tabellen

Beitrag von NicTheQuick »

Müsste das nicht sowas in der Art sein?

Code: Alles auswählen

SELECT adresse.vorname AS vorname, adresse.name AS name, sportler.swid AS swid, COUNT(meldung.id) AS anzahlMeldungen
  FROM sportler, adresse, wettkampf, meldung
  WHERE adresse.id = sportler.adresse
    AND meldung.sportler = sportler.id
    AND meldung.wettkampf = wettkampf.id
    AND sportler.aktiv = 1
    AND wettkampf.veranstaltung = 'Deine Veranstaltung';
Oder ist das nicht vergleichbar mit SQL?
Bild
Andesdaf
Moderator
Beiträge: 2658
Registriert: 15.06.2008 18:22
Wohnort: Dresden

Re: SQL-Abfrage über vier Tabellen

Beitrag von Andesdaf »

Hallo Nic,

soweit war ich auch schon (musste noch per GROUP BY gruppieren, damit Count hinten funktioniert.)
Allerdings gibt diese Abfrage halt nur diejenigen Sportler aus, die auch eine Meldung haben, ich brauche
aber zusätzlich auch alle anderen Sportler, quasi auch die mit anzahlMeldungen = 0.

Natürlich könnte ich das auch über zwei Abfragen machen, dann bräuchte ich aber wieder Hilfsstrukturen
und da ich die Abfrage relativ häufig auf einer größeren DB ausführe, wird das dann schnell träge. Deshalb
suche ich möglichst eine Komplettlösung.
Win11 x64 | PB 6.00 (x64)
Benutzeravatar
#NULL
Beiträge: 2235
Registriert: 20.04.2006 09:50

Re: SQL-Abfrage über vier Tabellen

Beitrag von #NULL »

kenn mich nicht mit Access aus, aber kannst du nicht 'meldung' und 'wettkampf' mit einem left join einbinden (mit ON clause statt dem WHERE part)? dann bekommst du ja auch rows wenn meldung.id NULL ist oder? notfalls den COUNT(meldung.id) noch mit COALESCE verbinden.
vielleicht hab ichs aber auch nicht verstanden :|
my pb stuff..
Bild..jedenfalls war das mal so.
Benutzeravatar
NicTheQuick
Ein Admin
Beiträge: 8675
Registriert: 29.08.2004 20:20
Computerausstattung: Ryzen 7 5800X, 32 GB DDR4-3200
Ubuntu 22.04.3 LTS
GeForce RTX 3080 Ti
Wohnort: Saarbrücken
Kontaktdaten:

Re: SQL-Abfrage über vier Tabellen

Beitrag von NicTheQuick »

Du hast Recht, das muss man mit LEFT JOIN machen, damit alle Sportler in der Auflistung drin bleiben. Da ich zu faul bin mir eine Beispiel-DB zum Testen zu basteln, hier ein weiterer Versuch:

Code: Alles auswählen

SELECT adresse.vorname AS vorname, adresse.name AS name, sportler.swid AS swid, COUNT(meldung.id) AS anzahlMeldungen
  FROM sportler
  INNER JOIN adresse
     ON adresse.id = sportler.adresse
  LEFT JOIN meldung
     ON meldung.sportler = sporler.id
  INNER JOIN wettkampf
     ON wettkampf.id = meldung.wettkampf
  WHERE 
    sportler.aktiv = 1
    AND wettkampf.veranstaltung = 'Deine Veranstaltung'
  GROUP BY sportler.id
Bild
Andesdaf
Moderator
Beiträge: 2658
Registriert: 15.06.2008 18:22
Wohnort: Dresden

Re: SQL-Abfrage über vier Tabellen

Beitrag von Andesdaf »

leider bekomme ich mit deinem Code einen Syntaxfehler nach dem
ersten Join ausgespuckt. Habe vergessen, zu erwähnen, dass ich ODBC
benutze, gibt das vielleicht Probleme?

Sorry, dass ich hier nicht genauer eingehen kann, aber bei mehreren Joins
komme ich dann doch an meine Verständnisgrenze :|
Win11 x64 | PB 6.00 (x64)
Benutzeravatar
NicTheQuick
Ein Admin
Beiträge: 8675
Registriert: 29.08.2004 20:20
Computerausstattung: Ryzen 7 5800X, 32 GB DDR4-3200
Ubuntu 22.04.3 LTS
GeForce RTX 3080 Ti
Wohnort: Saarbrücken
Kontaktdaten:

Re: SQL-Abfrage über vier Tabellen

Beitrag von NicTheQuick »

Ich habe keine Erfahrung mit MS Access und was da per ODBC möglich ist. Laut Doku scheint die Syntax etwas anders zu sein. Versuch es mal so:

Code: Alles auswählen

SELECT adresse.vorname AS vorname, adresse.name AS name, sportler.swid AS swid, COUNT(meldung.id) AS anzahlMeldungen
  FROM sportler
    INNER JOIN (adresse
      LEFT JOIN (meldung
        INNER JOIN wettkampf
          ON wettkampf.id = meldung.wettkampf)
      ON meldung.sportler = sporler.id)
    ON adresse.id = sportler.adresse     
  WHERE
    sportler.aktiv = 1 AND wettkampf.veranstaltung = 'Deine Veranstaltung'
  GROUP BY sportler.id
Bild
Benutzeravatar
#NULL
Beiträge: 2235
Registriert: 20.04.2006 09:50

Re: SQL-Abfrage über vier Tabellen

Beitrag von #NULL »

AND wettkampf.veranstaltung = 'Deine Veranstaltung'
wird vermutlich die null-rows wieder rausschmeißen, also noch mit OR wettkampf.id auf null prüfen oder die bedingung als ON clause umsetzen.
my pb stuff..
Bild..jedenfalls war das mal so.
Benutzeravatar
Kiffi
Beiträge: 10621
Registriert: 08.09.2004 08:21
Wohnort: Amphibios 9

Re: SQL-Abfrage über vier Tabellen

Beitrag von Kiffi »

@Andesdaf: Kannst Du vielleicht eine kleine Spiel-Datenbank zur Verfügung stellen? Damit lässt es sich leichter testen.

Grüße ... Peter
Hygge
Benutzeravatar
#NULL
Beiträge: 2235
Registriert: 20.04.2006 09:50

Re: SQL-Abfrage über vier Tabellen

Beitrag von #NULL »

beispiel:
sportler 1 hat 1 meldung
sportler 2 is inaktiv und fällt raus
sportler 3 hat 0 meldungen

Code: Alles auswählen

UseSQLiteDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
  Result = DatabaseUpdate(Database, Query$)
  If Result = 0
    Debug DatabaseError()
  EndIf
  
  ProcedureReturn Result
EndProcedure

DatabaseFile$ = GetTemporaryDirectory()+"Database.sqlite"

If CreateFile(0, DatabaseFile$)
  CloseFile(0)
  
  If OpenDatabase(0, DatabaseFile$, "", "")
    
    CheckDatabaseUpdate(0, "CREATE TABLE sportler (id int, swid int, aktiv int, adresseId int)")
    CheckDatabaseUpdate(0, "CREATE TABLE adresse (id int, vorname text, name text)")
    CheckDatabaseUpdate(0, "CREATE TABLE meldung (id int, sportlerId int, wettkampfId int)")
    CheckDatabaseUpdate(0, "CREATE TABLE wettkampf (id int, veranstaltung text)")
    
    CheckDatabaseUpdate(0, "INSERT INTO adresse (id, vorname, name) VALUES ('1', 'olli', 'humpi')")
    CheckDatabaseUpdate(0, "INSERT INTO adresse (id, vorname, name) VALUES ('2', 'hubi', 'bubi')")
    CheckDatabaseUpdate(0, "INSERT INTO adresse (id, vorname, name) VALUES ('3', 'popi', 'hobi')")
    
    CheckDatabaseUpdate(0, "INSERT INTO sportler (id, aktiv, adresseId) VALUES ('1', '1', '1')")
    CheckDatabaseUpdate(0, "INSERT INTO sportler (id, aktiv, adresseId) VALUES ('2', '0', '2')")
    CheckDatabaseUpdate(0, "INSERT INTO sportler (id, aktiv, adresseId) VALUES ('3', '1', '3')")
    
    CheckDatabaseUpdate(0, "INSERT INTO wettkampf (id, veranstaltung) VALUES (1, 'laufen')")
    CheckDatabaseUpdate(0, "INSERT INTO wettkampf (id, veranstaltung) VALUES (2, 'essen')")
    
    CheckDatabaseUpdate(0, "INSERT INTO meldung (id, sportlerId, wettkampfId) VALUES ('1', '1', '1')")
    CheckDatabaseUpdate(0, "INSERT INTO meldung (id, sportlerId, wettkampfId) VALUES ('2', '2', '2')")
    
    q.s = ""
    q + "select *, count(meldung.id) as countMeldung from ";sportler.*, adresse.vorname, adresse.name from "
    q + " sportler "
    q + " left join meldung on meldung.sportlerId = sportler.id "
    q + " left join wettkampf on wettkampf.id = meldung.wettkampfId "
    q + ",adresse "
    q + " where "
    q + " sportler.aktiv = 1 "
    q + " and sportler.adresseId = adresse.id "
    q + " group by sportler.id "
    If DatabaseQuery(0, q)
      
      cols = DatabaseColumns(0)
      
      s.s = ""
      For i=0 To cols-1
        s + " | " + LSet(DatabaseColumnName(0, i), 12, " ")
      Next
      Debug s
      
      While NextDatabaseRow(0)
        s.s = ""
        For i=0 To cols-1
          s + " | " + LSet(GetDatabaseString(0, i), 12 ," ")
        Next
        Debug s
      Wend
      
      FinishDatabaseQuery(0)
    EndIf
    
    CloseDatabase(0)
  Else
    Debug "Can't open database !"
  EndIf
Else
  Debug "Can't create the database file !"
EndIf

my pb stuff..
Bild..jedenfalls war das mal so.
Antworten