MySQL: Daten aus drei Tabellen zusammensuchen

[molily]

Hallo,

ich möchte eine im Grunde einfache Aufgabe lösen, da ich von solchen Datenbankoperationen, insbesondere JOINs, aber keine Ahnung habe und keine gute Einführung finde, bin ich nur durch Ausprobieren vorangekommen. Für Lektüretipps dazu wäre ich auch dankbar.

Gegeben sind drei Tabellen, in der einen stehen die Benutzerdaten, in der zweiten Meldungen (so etwas wie Posts) und in der dritten die Kommentare bzw. Ergänzungen zu den Meldungen (linear, wie in einem Board). Der Primärindex der Benutzertabelle ist loginname, die Meldungen haben ein entsprechendes loginname-Feld und den Primärindex msgid, die Kommentare haben entsprechende loginname- und msgid-Felder. So sind die Tabellen miteinander verknüpft.

Für die Übersichtsseite will ich nun die Meldungen auflisten. Dazu sollen aus der Meldungstabellen die Meldungsdaten geholt werden und einige zum jeweiligen loginnamen gehörenden Benutzerdaten aus der Benutzertabelle. Das ginge soweit mit einem Select über zwei Tabellen mit WHERE meldungen.loginname=benutzer.loginname. Zusätzlich will ich nun abfragen, wieviele Kommentare es in der Kommentartabelle zur jeweiligen Meldung gibt. Alles soll in einem Select abgehandelt werden und ich dachte mir, dass müsste gehen und ein Extraselect für die Kommentarzahle ist nicht nötig.

Ich bin soweit gekommen:

SELECT
meldungen.msgid,
/* ...Weitere Daten aus der meldungen-Tabelle... */
benutzer.vorname,
benutzer.nachname,
/* ...Evtl. weitere Daten aus der benutzer-Tabelle... */
COUNT(kommentare.msgid) AS kommentarzahl
FROM meldungen
LEFT JOIN benutzer ON meldungen.loginname=benutzer.loginname
LEFT JOIN kommentare ON meldungen.msgid=kommentare.msgid
WHERE meldungen.sichtbar="1"
GROUP BY meldungen.msgid
ORDER BY meldungen.aenderung DESC

Damit bekomme ich die Meldungsdaten, die zugehörigen Benutzerdaten und die jeweilige Kommentarzahl in einem Datensatz.

Das funktioniert soweit in den getesteten Fällen (Kommentare/keine Kommentare, Benutzer vorhanden/Benutzer nicht vorhanden, ...), scheint die Aufgabe zu erfüllen und scheint mir insgesamt logisch. Aber bekanntlich ist »es funktioniert« meist nicht hinreichend und vielleicht gibt es Fälle, die ich nicht abschätzen kann. Ist dies also eine effiziente Lösung? Lässt es sich besser lösen? Ist das Konzept bereits ineffizient?

Mathias

[Dennis]

Hi @all!

ich möchte eine im Grunde einfache Aufgabe lösen, da ich von solchen Datenbankoperationen, insbesondere JOINs, aber keine Ahnung habe und keine gute Einführung finde, bin ich nur durch Ausprobieren vorangekommen. Für Lektüretipps dazu wäre ich auch dankbar.

Bitte, kannst du haben: http://tut.php-q.net/ hat mir sehr geholfen, bzw auch http://faq.php-q.net/
http://de.php.net/manual/de/ dürfest du zum Nachschlagen vermutlich schon kennen.

Viel Spaß,
MfG, Dennis.

[molily]

Hallo,

ich möchte eine im Grunde einfache Aufgabe lösen, da ich von solchen Datenbankoperationen, insbesondere JOINs, aber keine Ahnung habe und keine gute Einführung finde, bin ich nur durch Ausprobieren vorangekommen. Für Lektüretipps dazu wäre ich auch dankbar.
Bitte, kannst du haben: http://tut.php-q.net/ hat mir sehr geholfen, bzw auch http://faq.php-q.net/
http://de.php.net/manual/de/ dürfest du zum Nachschlagen vermutlich schon kennen.

Danke, aber ich suche ja nichts zu PHP bzw. MySQL allgemein, sondern etwas Konkretes zu MySQL-Joins, wie sie für die Aufgabe relevant sind. Ich war zwar schon fündig geworden, die gängigen Quellen kenne ich, darüber bin ich auf die besagte Möglichkeit gestoßen, aber wirklich fundiert und erhellend war das nicht. Bzw. es erscheint mir soweit einleuchtend, aber vielleicht liege ich daneben.
Höchstens http://tut.php-q.net/mysql-id.html behandelt das Thema grob, aber das dort erläuterte Grundschema der Beziehung habe ich schon verstanden.

Mathias

[Axel Richter]

Hallo,

Ich bin soweit gekommen:

SELECT
meldungen.msgid,
/* ...Weitere Daten aus der meldungen-Tabelle... */
benutzer.vorname,
benutzer.nachname,
/* ...Evtl. weitere Daten aus der benutzer-Tabelle... */
COUNT(kommentare.msgid) AS kommentarzahl
FROM meldungen
LEFT JOIN benutzer ON meldungen.loginname=benutzer.loginname
LEFT JOIN kommentare ON meldungen.msgid=kommentare.msgid
WHERE meldungen.sichtbar="1"
GROUP BY meldungen.msgid
ORDER BY meldungen.aenderung DESC

Damit bekomme ich die Meldungsdaten, die zugehörigen Benutzerdaten und die jeweilige Kommentarzahl in einem Datensatz.

Das funktioniert soweit in den getesteten Fällen (Kommentare/keine Kommentare, Benutzer vorhanden/Benutzer nicht vorhanden, ...)

Es gibt also wirklich in der Tabelle meldungen Einträge, zu denen es keine Entsprechungen in der Tabelle benutzer gibt? Also Meldungen von nicht vorhandenen Benutzern? Outer-Joins, wie LEFT JOIN und RIGHT JOIN, sind nämlich in jedem Fall langsamer als INNER JOINs. Sollte meine Frage also mit Nein zu beantworten sein, wäre folgendes besser:

SELECT
meldungen.msgid,

benutzer.vorname,
benutzer.nachname,

Count(kommentare.msgid) AS AnzahlKommentare
FROM
(meldungen INNER JOIN benutzer ON meldungen.loginname = benutzer.loginname)
LEFT JOIN kommentare ON meldungen.msgid = kommentare.msgid
GROUP BY meldungen.msgid, benutzer.vorname, benutzer.nachname;

Die vielen Felder nach GROUP BY sind so nach SQL vorgeschrieben. Demnach muss in gruppierten Abfragen jedes Feld entweder zur Grupperung gehören oder Teil einer Aggregatfunktion sein. Es ginge also auch so:
SELECT meldungen.msgid, First(benutzer.vorname) AS ErsterVorname, First(benutzer.nachname) AS ErsterNachname, Count(kommentare.msgid) AS AnzahlKommentare
FROM (meldungen INNER JOIN benutzer ON meldungen.loginname = benutzer.loginname) LEFT JOIN kommentare ON meldungen.msgid = kommentare.msgid
GROUP BY meldungen.msgid;
Wobei ich jetzt nicht weiß, ob MySQL die Aggregatfunktion First() kennt. MySQL akzeptiert die Abfrage auch so, wie Du sie gruppiert hast, nimmt aber auch ohne explizite Aggregatfunktion jeweils den ersten Wert der Felder, nach denen nicht gruppiert wird.

Wenn meine Frage mit Ja zu beantworten ist, geht es natürlich nur so, wie Du es bereits machst. Dann vergiss aber nicht Indexe auf die Felder zu setzen, die zu den JOIN-Verknüpfungen gehören und nach denen gruppiert und sortiert wird.

viele Grüße

Axel

[molily]

Hallo Axel,

Das funktioniert soweit in den getesteten Fällen (Kommentare/keine Kommentare, Benutzer vorhanden/Benutzer nicht vorhanden, ...)
Es gibt also wirklich in der Tabelle meldungen Einträge, zu denen es keine Entsprechungen in der Tabelle benutzer gibt? Also Meldungen von nicht vorhandenen Benutzern?

Regulär nicht, es wäre der Ausnahmefall. Wenn jemand eine Meldung verfasst und irgendwann später seinen Konto löschen möchte, wäre es mir lieb, den gesamten Eintrag in der Benutzertabelle löschen zu können, anstatt den Eintrag nur durch einen Flag zu deaktivieren (so eine Spalte habe ich schon, allerdings war sie für andere Fälle gedacht) und ihn dann als Karteileiche zu behalten, die nur dazu da ist, damit bei alten Kommentaren und Meldungen die Namen korrekt angezeigt werden. Ich nehme an, dass das Löschen eines Benutzers, unter dessen Loginnamen vorher großartig Meldungen und Kommentare verfasst wurden, extrem unwahrscheinlich ist. Im Prinzip ist es mir egal, dass bei diesen Meldungen und Kommentaren dann nur noch die Loginnamen angezeigt wird, weil dem Meldungs-Loginnamen keine Zeile mehr in de Benutzertabelle entspricht.

Ich weiß nicht, wie sollte ich das lösen?

Outer-Joins, wie LEFT JOIN und RIGHT JOIN, sind nämlich in jedem Fall langsamer als INNER JOINs.

Ja, das hatte ich gelesen, daher war der Outer Join bewusst gewählt.

Sollte meine Frage also mit Nein zu beantworten sein, wäre folgendes besser:

SELECT
meldungen.msgid,

benutzer.vorname,
benutzer.nachname,

Count(kommentare.msgid) AS AnzahlKommentare
FROM
(meldungen INNER JOIN benutzer ON meldungen.loginname = benutzer.loginname)
LEFT JOIN kommentare ON meldungen.msgid = kommentare.msgid
GROUP BY meldungen.msgid, benutzer.vorname, benutzer.nachname;

Ist die Klammerung des ersten Joins im Allgemeinen wichtig bzw. ratsam?

Die vielen Felder nach GROUP BY sind so nach SQL vorgeschrieben. Demnach muss in gruppierten Abfragen jedes Feld entweder zur Grupperung gehören oder Teil einer Aggregatfunktion sein.

Also sollte ich, wenn ich die Spalten
 meldungen.msgid, DATE_FORMAT(meldungen.time, "%d.%m.%Y, %H:%i Uhr") AS time, DATE_FORMAT(meldungen.aenderung, "%d.%m.%Y, %H:%i Uhr") AS aenderung, meldungen.zugriff, meldungen.loginname, meldungen.meldungstitel, meldungen.meldungstext,
 benutzer.vorname, benutzer.nachname,
 COUNT(kommentare.msgid) AS kommentarzahl
selecte, sie unter GROUP BY entsprechend aufführen bis auf die kommentarzahl?
 GROUP BY meldungen.msgid, time, aenderung, meldungen.zugriff, meldungen.loginname, meldungen.meldungstitel, meldungen.meldungstext, benutzer.vorname, benutzer.nachname

Es ginge also auch so:
[...]
Wobei ich jetzt nicht weiß, ob MySQL die Aggregatfunktion First() kennt.

Offenbar nicht...

MySQL akzeptiert die Abfrage auch so, wie Du sie gruppiert hast, nimmt aber auch ohne explizite Aggregatfunktion jeweils den ersten Wert der Felder, nach denen nicht gruppiert wird.

Was hat es mit dieser Gruppierung auf sich? Mir scheint die Gruppierung nach meldungen.msgid ausreichend, wenn ich mir das Joinen veranschauliche.

Wenn meine Frage mit Ja zu beantworten ist, geht es natürlich nur so, wie Du es bereits machst. Dann vergiss aber nicht Indexe auf die Felder zu setzen, die zu den JOIN-Verknüpfungen gehören und nach denen gruppiert und sortiert wird.

Aha, das hatte ich vermutet.

Mathias

[Axel Richter]

Hallo Mathias,

Es gibt also wirklich in der Tabelle meldungen Einträge, zu denen es keine Entsprechungen in der Tabelle benutzer gibt? Also Meldungen von nicht vorhandenen Benutzern?

Regulär nicht, es wäre der Ausnahmefall. Wenn jemand eine Meldung verfasst und irgendwann später seinen Konto löschen möchte, wäre es mir lieb, den gesamten Eintrag in der Benutzertabelle löschen zu können, anstatt den Eintrag nur durch einen Flag zu deaktivieren (so eine Spalte habe ich schon, allerdings war sie für andere Fälle gedacht) und ihn dann als Karteileiche zu behalten, die nur dazu da ist, damit bei alten Kommentaren und Meldungen die Namen korrekt angezeigt werden.

Das ist Ansichtssache. Ich würde das nicht als Karteileiche bezeichnen. Immerhin gibt es für diesen Nutzer ja noch Meldungen und zu diesen Meldungen eventuell Kommentare. Das Prinzip der referentiellen Integrität würde es eigentlich gebieten, mit dem Nutzer auch die zugehörigen Meldungen und die dazu gehörenden Kommentare zu löschen. Da das, in Deinem Fall, sicherlich nicht gewollt ist, solltest Du die Nutzerdaten auch behalten. Das bringt eben den Vorteil, dass Du zur Verbindung der Tabellen meldungen und benutzer einen INNER JOIN benutzen kannst.

SELECT
meldungen.msgid,

benutzer.vorname,
benutzer.nachname,

Count(kommentare.msgid) AS AnzahlKommentare
FROM
(meldungen INNER JOIN benutzer ON meldungen.loginname = benutzer.loginname)
LEFT JOIN kommentare ON meldungen.msgid = kommentare.msgid
GROUP BY meldungen.msgid, benutzer.vorname, benutzer.nachname;

Ist die Klammerung des ersten Joins im Allgemeinen wichtig bzw. ratsam?

Nein, die mache ich nur zur Übersicht. SQL arbeitet Joins von links nach rechts ab.

Die vielen Felder nach GROUP BY sind so nach SQL vorgeschrieben. Demnach muss in gruppierten Abfragen jedes Feld entweder zur Grupperung gehören oder Teil einer Aggregatfunktion sein.

Also sollte ich, wenn ich die Spalten
 meldungen.msgid, DATE_FORMAT(meldungen.time, "%d.%m.%Y, %H:%i Uhr") AS time, DATE_FORMAT(meldungen.aenderung, "%d.%m.%Y, %H:%i Uhr") AS aenderung, meldungen.zugriff, meldungen.loginname, meldungen.meldungstitel, meldungen.meldungstext,
 benutzer.vorname, benutzer.nachname,
 COUNT(kommentare.msgid) AS kommentarzahl
selecte, sie unter GROUP BY entsprechend aufführen bis auf die kommentarzahl?
 GROUP BY meldungen.msgid, time, aenderung, meldungen.zugriff, meldungen.loginname, meldungen.meldungstitel, meldungen.meldungstext, benutzer.vorname, benutzer.nachname

Ja, das wäre Standard-ANSI-SQL-konform. Eine gute online-Übersicht zu SQL habe ich auch noch nicht gefunden. Die Bücher, die ich hierzu habe, sind auch alle schon etwas angejahrt, weshalb ich sie nicht empfehle.

MySQL akzeptiert die Abfrage auch so, wie Du sie gruppiert hast, nimmt aber auch ohne explizite Aggregatfunktion jeweils den ersten Wert der Felder, nach denen nicht gruppiert wird.

Was hat es mit dieser Gruppierung auf sich? Mir scheint die Gruppierung nach meldungen.msgid ausreichend, wenn ich mir das Joinen veranschauliche.

Es steckt folgendes dahinter:
Du joinst z.B. meldungen, benutzer und kommentare und wählst die Felder
meldungen.msgid, meldungen.time, meldungen.aenderung, meldungen.zugriff, meldungen.loginname, meldungen.meldungstitel, meldungen.meldungstext, benutzer.vorname, benutzer.nachname, COUNT(kommentare.msgid) AS kommentarzahl

Nun kann es doch sein, dass es zu einer Meldungen mehrere Kommentare gibt. Sonst wäre ja das COUNT unsinnig. Der Join ergibt also zunächst eine Datensatzmenge, mit sovielen Datensätzen in denen die selben Daten für meldungen.msgid, meldungen.time, meldungen.aenderung, meldungen.zugriff, meldungen.loginname, meldungen.meldungstitel, meldungen.meldungstext, benutzer.vorname, benutzer.nachname stehen, wie es Kommentare zu dieser Meldung gibt. Nach meldungen.msgid wird nun gruppiert und COUNT zählt die Anzahl der gruppierten Datensätze (Ja, im Prinzip macht es genau das. Es ist nur zufällig gleich der Anzahl der kommentare.msgid ;-)). Daraus ergibt sich das endgültige Resultset, mit genau _einem_ Wert für meldungen.msgid und COUNT je Datensatz. Welcher Wert aus meldungen.time, meldungen.aenderung, meldungen.zugriff, meldungen.loginname, meldungen.meldungstitel, meldungen.meldungstext, benutzer.vorname, benutzer.nachname soll aber nun in den Datensätzen dieses Resultsets stehen? Gibt es z.B. drei gleiche meldungen.msgid, dann stehen jeweils drei Datensätze mit Werten für die andern Felder zur Verfügung. In Deinem Fall sind diese Werte alle identisch, weshalb Du auch einfach nach allen gruppieren kannst. Das muss aber nicht so sein. Deshalb ist es vorgeschrieben, dass man in der SQL-Abfrage festlegt, welche Werte der nicht gruppierten Felder man haben möchte. Das geschieht dann mit den Aggregatfunktionen First() bzw. Last(). MySQL nimmt eben automatisch den ersten (First()) der drei Datensätze als Quelle.

viele Grüße

Axel

[Ilja]

yo,

Alles soll in einem Select abgehandelt werden und ich dachte mir, dass müsste gehen und ein Extraselect für die Kommentarzahle ist nicht nötig.

erstens geht es unter den kriterien, die du gestellt hast nicht. und zweitens in eine abfrage nicht immer besser als mehrere abfragen, insbesondere nicht in deinem fall. du gehst die sache zu kompliziert an, trennen den count() von der ersten abfrage. das bringt übersichtlichkeit und performance und vor allem es funktioniert auch.

des weiteren vermute ich, ist dein group by nicht notwendig. es gibt ja laut definition keine gleichen msgid wenn ich mich nicht täusche. insofern kann er auch keine gleichen finden, nach denen er gruppieren könnte.

Ilja

[molily]

Hallo,

Alles soll in einem Select abgehandelt werden und ich dachte mir, dass müsste gehen und ein Extraselect für die Kommentarzahle ist nicht nötig.

erstens geht es unter den kriterien, die du gestellt hast nicht.

Wieso?

und zweitens in eine abfrage nicht immer besser als mehrere abfragen, insbesondere nicht in deinem fall. du gehst die sache zu kompliziert an, trennen den count() von der ersten abfrage. das bringt übersichtlichkeit und performance und vor allem es funktioniert auch.

Ja, soweit war ich schon, ich fragte mich eben nur, ob es auch mit einer Abfrage abgehandelt werden kann. Für solche Aufgaben scheinen mir Joins prädestiniert. Eine Anfrage kann ich übersichtlicher handhaben als mehrere, ich habe also PHP-Code eingespart.
Die Gesamtperformance habe ich nicht getestet, es stimmt aber, dass eine Abfrage an sich geringfügig langsamer ist als zwei, die letztlich dieselben Daten abfragen. Hinzu käme im Falle von zwei Abfragen die nötige Verarbeitung der ersten Abfrage und das Zusammenbringen der Daten, das bei der kombinierten Abfrage wegfällt (da habe ich die Kommentarzahl direkt im selben Datensatz). Insofern sehe ich hinsichtlich Performance keine klare Tendenz.

des weiteren vermute ich, ist dein group by nicht notwendig.

Wie meinst du das? Wenn ich COUNT() funktioniert hier nicht ohne GROUP BY, MySQL wird ein Fehler zurückgeben, nähme ich es einfach heraus.

es gibt ja laut definition keine gleichen msgid wenn ich mich nicht täusche. insofern kann er auch keine gleichen finden, nach denen er gruppieren könnte.

Ich verstehe nicht, was du meinst. Gleiche msgids finden sich wie gesagt in den Tabellen meldungen und kommentare, falls du darauf hinauswillst. Die Kommentare sind über die msgid mit den Meldungen verknüpft. Wie gesagt werden diese gleichen auch gefunden, die Kommentare mit gleicher msgid werden gezählt und durch den Join in die mit den Benutzerdaten gejointen Meldungs-Zeilen mit den jeweiligen msgids einsortiert.

Mathias

[Ilja]

yo,

erstens geht es unter den kriterien, die du gestellt hast nicht.

Wieso?

weil du zum einen bestimmte spalten anzeigen willst und gleichzeitig eine aggregat-funktion anwendest. das snd gleich zwei wünsche auf einmal und das geht nun wirklich nicht.

spass beseite, aggregat-funktionen haben die eigenschaft nur einen wert zurückzuliefern. demzufolge müssen alle spalten, die gleichzeitg angezeigt werden auch in der group by klausel stehen, weil sie sonst mehrere werte zurückliefern könnten und das würde im widerspruch stehen zu dem nur einen wert der aggregat funktion. nimmst du nun aber alle spalten in die group by klausel auf, da verändert das das verhalten von count(), da ja anderes gruppiert wird. ergo es geht nicht.

Ja, soweit war ich schon, ich fragte mich eben nur, ob es auch mit einer Abfrage abgehandelt werden kann. Für solche Aufgaben scheinen mir Joins prädestiniert. Eine Anfrage kann ich übersichtlicher handhaben als mehrere, ich habe also PHP-Code eingespart.

ganz und gar nicht. erstens sind join kein allheilmittel, sondern sollten nur gezielt eingesetzt werden. sie sind nämlich nicht besonders perfomant. zum anderen ist eine größere abfrage wesentlich unübersichtlicher als zwei kleine abfragen.

Ilja