SQL Abfrage aus Relationstabelle

[uburoi]

Hallo zusammen!

Ich stehe gerade etwas auf dem Schlauch, daher würde ich gerne die hier gesammelte Kompetenz anzapfen …

Momentan bastele ich an einem Programm, mit dem ich meine Bibliothek verwalten will. In der Maske (Screenshot 1 A) kann ich mehrere Autoren für einen Titel eingeben; beim Einpflegen in die Datenbank (ER-Modell siehe Screenshot 2) werden die Autoren separat gespeichert. Die Autoren werden dann in einer Relationstabelle mit dem entsprechenden Titel verknüpft.

Nun meine Frage: Wenn ich die Titel mitsamt Autoren aus der Datenbank abrufe, möchte ich gerne wieder beide/alle Autoren eines Titels im Autor-Feld der Liste ausgeben (Screenshot 1 B). Jetzt verknüpfe ich bei der Abfrage der Relationstabelle ja erstmal die übrigen Tabellen mit dem JOIN-Befehl, etwa:

SELECT a.Nachname, t.Titel
FROM Rel_Autor AS r
JOIN Autor AS a ON a.AID = r.AID
JOIN Titel AS t ON t.TitelID = r.TitelID

Hier habe ich aber dann das Problem, dass die Autoren mit Titel einzeln im Ergebnis stehen (etwa: "Deleuze | Anti-Ödipus" (Zeile 1), "Guattari | Anti-Ödipus" (Zeile 2)). Stattdessen hätte ich aber gerne alle Autoren in einem einzigen Feld (also: "Deleuze, Guattari | Anti-Ödipus").
Natürlich kann ich das Ergebnis der Abfrage dann im Programm entsprechend aufbereiten, was aber bei einer höheren Anzahl an Ergebnissen sicherlich weniger performant ist als eine Datenbankabfrage. Daher überlege ich nun, ob ich nicht mit einer Datenbank-Abfrage direkt das gewünschte Ergebnis erzielen kann. Hat jemand eine Idee?

(Ich hoffe, ich konnte mein Problem verständlich machen – ich bin nur Hobbyprogrammierer, kein Profi …)

Gruß und vielen Dank vorweg!
Jens

 

[mTw Raptor]

Da ich grade nur am Handy tippe, wird das etwas kürzer:

SELECT a.Nachname + " | " + t.Titel AS "Neuer Feldname" FROM Rel_Autor AS r JOIN Autor AS a ON a.AID = r.AID JOIN Titel AS t ON t.TitelID = r.TitelID

Das würde so zumindest unter Oracle SQL funktionieren.

 

[tollertyp]

Meiner Meinung nach, wenn dir das extrem wichtig ist, dann würde ich jedem Buch einfach einen Autoren-VARCHAR verpassen, den du bei jeder Änderung der Autoren halt aktualisieren müsstest. Ist eine hässliche Redundanz, aber eigentlich ändern sich Autoren-Daten auch nicht häufig.

@mTw Raptor: Ich glaube du hast das Problem nicht verstanden.
Es geht darum, dass ein Buch mehrere Autoren haben kann, aber jedes Buch nur einmal im Ergebnis auftauchen soll mit allen Autoren in einer Ergebnis-Spalte.

Ich gebe aber zu, explizit und deutlich zu sagen, dass das Problem ist, dass ein Buch mehrere Autoren haben kann, wäre sicher angebracht gewesen.

 

[Freezedevil]

Du brauchst eine Group By über die Titel und dann eine entsprechende Aggregationsfunktion für die Autoren. In MySQL gibt es dafür zB GROUP_CONCAT https://www.geeksforgeeks.org/mysql-group_concat-function/
Musst du mal für dein DBMS das entsprechende Äquivalent raussuchen.

 

[tollertyp]

Ah okay die Aggregierungsfunktion kannte ich noch nicht, an Group By hab ich auch gedacht.

 

[uburoi]

Ich würde die Datenbank schon gerne normgerecht halten, da ich sonst an anderen Enden Schwierigkeiten bekomme.

Mit GROUP_CONCAT habe ich ein bisschen herumprobiert, und folgende Abfrage liefert mir genau das Ergebnis, das ich haben möchte:

SELECT t.TitelID, GROUP_CONCAT(a.Nachname || ', ' || a.Vorname, '; ') AS Nachname, t.Titel, t.Jahr
FROM Rel_Autor AS r
JOIN Autor AS a ON a.AID = r.AID
JOIN Titel AS t ON t.TitelID = r.TitelID
GROUP BY t.Titel
ORDER BY Nachname, t.Titel

Jetzt habe ich nur noch das Anschlussproblem, dass manche Autoren keinen Nachnamen haben (z. B. Platon) und dann kein Komma mit (leerem) Vornamen angehängt werden soll. Mal sehen, wie ich das noch löse. 🙈

Herzlichen Dank für die Hilfe!

Gruß Jens

 

[tollertyp]

Wobei ich auch grundsätzlich sagen würde, dass ich so etwas gar nicht zwingend auf SQL-Ebene lösen würde, weil aus meiner Sicht es ein reines Anzeige-Thema ist, das du da hast, und das in der SQL-/Datenzugriffsschicht eigentlich gar nichts verloren hat. Außer es ist dermaßen leistungsrelevant. Also ich finde es halt wenig elegant, bei einer Abfrage der Bücher die Information über die einzelnen Autoren zu verlieren.

Und je nachdem, mit was du programmierst, ist die Frage, ob du nicht eine Persistenzschicht verwenden kannst, die die manuellen SQL-Befehle "überflüssig" macht.

Zum Lernen ist es natürlich schon gut, auch solche Themen mal von Hand zu schreiben.

 

[uburoi]

In der Methode, die ich gerade programmiere, geht es tatsächlich auch nur um reine Anzeige. Die Datenverarbeitung geschieht an anderer Stelle, und da werden die Daten auch sauber auseinandergehalten.

Ich programmiere mit Xojo (ein BASIC-Dialekt). Die SQL-Abfragen sind höllisch schnell, eine "manuelle" Zusammenführung und Sortierung des Ergebnisses wäre da sicherlich deutlich zeitintensiver (erfahrungsgemäß; habe es jetzt im konkreten Fall nicht ausprobiert). Mehr als 2000 oder 3000 Titel werden auch nie in der Datenbank enthalten sein, aber ich möchte die Gesamtanzeige in der Liste so schnell wie möglich haben, weshalb ich die Arbeit in diesem Fall auf die SQL-Ebene verlegen möchte.

Ich bin jetzt mit dieser Variante fürs Erste zufrieden:

SELECT t.TitelID, GROUP_CONCAT(IIF(a.Nachname <> '', a.Nachname || ', ' || a.Vorname, a.Vorname), '; ') AS Nachname, t.Titel, t.Jahr
FROM Rel_Autor AS r
JOIN Autor AS a ON a.AID = r.AID
JOIN Titel AS t ON t.TitelID = r.TitelID
GROUP BY t.Titel
ORDER BY Nachname, t.Titel

Aus professioneller Sicht mag das ziemlich hemdsärmelig erscheinen, aber es erfüllt erstmal seinen Zweck. Trotzdem noch einmal vielen Dank für die Anregungen!

Gruß Jens

 

[Freezedevil]

Es ist immer sinnvoll so viel wie möglich an die Datenbank abzudrücken. Warum sollte ich ein Ergebnis anfragen das, wie im Beispiel hier, aufgrund von Redundanzen (Titelinformationen für jeden Autor) viel größer ist als das was ich eigentlich haben möchte und auf dem ich anschließend auch noch selbst rumrödeln muss. Das ergibt aus keiner Sicht Sinn. Zumal der Optimizer eines DBMS um einiges cleverer ist als das was man mal eben Adhoc auf die Beine stellt.

Deine Anfrage ist keineswegs hemdsärmlich. Allerdings solltest du eher nach TitelID statt Titel gruppieren.

 

[tollertyp]

Also du müsstest schon arg schlampig programmieren, dass die Sortierung in deiner Anwendung zu spürbaren Leistungsproblemen führt :-)

Die Frage ist: Was ist wichtiger? Wartbarkeit oder Performance? Aber wenn es einen Performance-First-Ansatz gibt, dann soll man ihn halt fahren.

Das Gruppen nach Titel war wohl ein Überbleibsel, weil er die TitelID wohl erst später dazu gepackt hat.

 

[Freezedevil]

Inwiefern erhöht denn eine unpräzise Anfrage + Code, um dann das eigentlich gewünschte Ergebnis zu erhalten, die Wartbarkeit gegenüber einer einzigen Anfrage?

 

[uburoi]

@Freezedevil: Danke für den Hinweis. Wird eingearbeitet.

@tollertyp: Die vorhandenen Sortieralgorithmen brechen schnell ein, wenn man verschiedene Sortierkriterien verknüpft, und selbst einen zu schreiben, bin ich zu faul (und es lenkt mich auch von dem ab, was ich eigentlich machen will).

Gruß Jens

 

[tollertyp]

Welche verschiedenen Sortierkrierien kommen denn da zusammen?
Aus meiner Sicht ist die Sortierung bei deiner Abfrage kaputt. Warum? Kommen die Autoren denn immer in erwarteter alphabetischer Reihenfolge? Bzw. in welcher Reihenfolge kommen die Autoren in das aggregierte Nachname-Feld, das nicht der Nachname ist?

@Freezedevil: Wartbarkeit, wenn z.B. noch ein Mittelname dazu kommt... soll es ja auch geben. Generell, nach welchem Autor soll denn da sortiert werden? Der, der zufällig (oder deterministisch) an der ersten Stelle steht?

 

[uburoi]

Ich bin jetzt erstmal weg, kann aber gerne später noch etwas dazu schreiben …

Gruß Jens

 

[tollertyp]

Also ist auch eher eine akademische Diskussion. Ist ja schon okay, dass du es so machst, wie es dir recht ist.

 

[Freezedevil]

Was die Sortierung angeht hast du vollkommen Recht. Die ist aktuell von den Interna des DBMS abhängig und muss als zufällig angesehen werden. Dass die Anfrage Schwächen enthält ist aus meiner Sicht allerdings noch kein Argument dafür nicht möglichst viel in die Anfrage zu stecken. Wenn ein Mittelname dazu kommt, muss ich meinen eigenen Code ja auch dahingehend anpassen - genau wie die Anfrage.
Eine akademische Diskussion ist durchaus hilfreich, um was zu lernen. Eventuell ist ihm die Anfrage so tatsächlich nicht recht, aber es ist ihm nicht bewusst.
Bin jetzt aber auch erst einmal raus.

 

[Testa2014]

Es ist immer sinnvoll so viel wie möglich an die Datenbank abzudrücken

wenn man jetzt noch einen oben drauf setzen möchte: Views. Statt die selects immer direkt abzufeuern.

 

[uburoi]

An der Sortierung muss ich sicher noch feilen (zumal auch noch das Problem der Umlaute lauert), aber die möchte ich zu guter Letzt eben der Datenbank überlassen. Die Autoren liegen wild verstreut in der Tabelle, da die Autoren eher selten Koautoren sind, sondern in der Regel mehrheitlich eigene Titel verfasst haben. Wenn es mehrere Autoren gibt, sollen sie alphabetisch aufsteigend konkatiniert werden und sich insgesamt wiederum in die Sortierung der Autoren einreihen.

@Testa2014: Was meinst du genau?
Wenn es um Entwurfsmuster geht (da bin ich noch ziemlich weit am Anfang): Die GUI hat keine direkte Datenbankanbindung, sondern benachrichtigt nur über ein Observer-Pattern den Controller, wenn es Änderungen gibt. Der Controller holt sich dann die fertigen Daten von der Model-Klasse, die alle Datenbank-Geschichten erledigt, und reicht sie an die GUI weiter.

Gruß Jens

Ergänzung (4. Oktober 2021)

Nachtrag: Ich habe das Ganze jetzt noch dahingehend geändert, dass Einzelnamen wie Platon im Nachnamen gespeichert werden, und testweise ein paar Dummy-Bücher eingepflegt (in der Reihenfolge der Titel 4, 3, 2, 1; s. Screenshot). Die Ausgabe entspricht dabei genau meinen Vorstellungen – mal sehen, ob da noch unvorhergesehene Ausnahmen reingrätschen (Umlaute im Anlaut werden sicher noch ein Thema), aber erstmal kann das so bleiben:

SELECT t.TitelID, GROUP_CONCAT(IIF(a.Vorname <> '', a.Nachname || ', ' || a.Vorname, a.Nachname), '; ') AS Namen, t.Titel, t.Jahr
FROM Rel_Autor AS r
JOIN Autor AS a ON a.AID = r.AID
JOIN Titel AS t ON t.TitelID = r.TitelID
GROUP BY t.TitelID
ORDER BY Namen, t.Titel

Gruß Jens

 

[Freezedevil]

Die Reihenfolge der Autoren, die du bekommst, entspricht vermutlich der Reihenfolge in der sie eingefügt wurden. Ohne explizite Sortierung gibt es dafür allerdings keine Garantie. Updates/Deletes/Inserts können die Reihenfolge ändern. Ebenso legt ein anderes DBMS die Daten intern evtl anders ab und liefert die Sätze entsprechend in einer anderen Reihenfolge zurück (relevant falls du irgendwann mal wechseln wollen solltest). Ohne Order By muss die Reihenfolge als zufällig angenommen werden ansonsten bekommt man früher oder später Probleme.

Mit View meint Testa https://de.wikipedia.org/wiki/Sicht_(Datenbank)

 

[uburoi]

Ah, dieses View kannte ich noch nicht. Sehe ich mir gleich mal genauer an.

Für die Sortierproblematik finde ich bestimmt auch noch eine Lösung, werde weiter daran basteln. Ich bin schon mal froh, dass ich auf die Möglichkeit der Aggregierung gestoßen wurde.

Gruß Jens