Query gesucht

[Stefan]

Hallo,

Ich habe 2 Tabellen, eine mit Spielern und eine mit Spielen. Die Spieler-ID wird in der Spiele-Tabelle geführt, wenn der Spieler an diesem Spiel teilgenommen hat.
Jetzt möchte ich prüfen, ob es Spieler gibt, die bis zum jetztigen Zeitpunkt an allen Spielen teilgenommen haben. Im komm aber nicht drauf.

Stefan

[dedlfix]

Tach!

Ich habe 2 Tabellen, eine mit Spielern und eine mit Spielen. Die Spieler-ID wird in der Spiele-Tabelle geführt, wenn der Spieler an diesem Spiel teilgenommen hat.

Fehlt da nicht noch eine Tabelle? Es gibt Spieler und Spiele und Spieler, die an Spielen teilgenommen haben. Jeder Spieler kann an mehreren Spielen teilnehmen und in einem Spiel sind mehrere Spieler. Also ist das eine m:n-Beziehung, die man mit einer dritten Tabelle abbildet.

Jetzt möchte ich prüfen, ob es Spieler gibt, die bis zum jetztigen Zeitpunkt an allen Spielen teilgenommen haben. Im komm aber nicht drauf.

Definiere zunächst "alle Spiele". Wenn in der m:n-Tabelle ein Unique-Key (oder auch Primary-Key) über beide Spalten gelegt wird, ist sichergestellt, dass keine Dopplungen vorkommen können. Dann wäre Gruppieren über den Spieler und Zählen der Spiele ein Weg zur Lösung. Dazu noch die Anzahl der Spiele zählen.

dedlfix.

[Stefan]

Tach!

Ich habe 2 Tabellen, eine mit Spielern und eine mit Spielen. Die Spieler-ID wird in der Spiele-Tabelle geführt, wenn der Spieler an diesem Spiel teilgenommen hat.

Fehlt da nicht noch eine Tabelle?

Prinzipiell natürlich schon. Es ist ein Gedankenexperiment, nichts produktives.

Ich nehme an, daß die Spielertabelle nur die ID, sowie Name enthält.
Die Spieletabelle würde die Spiele ID und die Spieler-ID enthalten.
Die 3. (fehlende) Tabelle enthält alle Spiele und besteht aus Spiel-ID und sonstigen Angaben zum Spiel.

Definiere zunächst "alle Spiele".

Siehe Tabelle 3. Da sind alle Spiele aufgelistet.

Wenn in der m:n-Tabelle ein Unique-Key (oder auch Primary-Key) über beide Spalten gelegt wird, ist sichergestellt, dass keine Dopplungen vorkommen können.

Würde ich in einem Produktivsystem machen.

Dann wäre Gruppieren über den Spieler und Zählen der Spiele ein Weg zur Lösung. Dazu noch die Anzahl der Spiele zählen.

Geht es ein bißchen konkreter?

Stefan

[dedlfix]

Tach!

Ich nehme an, daß die Spielertabelle nur die ID, sowie Name enthält.
Die Spieletabelle würde die Spiele ID und die Spieler-ID enthalten.
Die 3. (fehlende) Tabelle enthält alle Spiele und besteht aus Spiel-ID und sonstigen Angaben zum Spiel.

So wäre das eine nützliche Struktur.

Definiere zunächst "alle Spiele".
Siehe Tabelle 3. Da sind alle Spiele aufgelistet.

Ich meine, wie stellst du fest, dass jemand an allen Spielen teilgenommen hat? Wie würdest du das im "normalen Leben" tun? Reicht es, zu zählen an wievielen Spielen ein Spieler teilgenommen hat und das mit der Anzahl der Spiele zu vergleichen oder musst du jeden Spieler zu den jeweiligen Spielen zuordnen und schauen, wo solch eine Zuordnung fehlt?

Wenn in der m:n-Tabelle ein Unique-Key (oder auch Primary-Key) über beide Spalten gelegt wird, ist sichergestellt, dass keine Dopplungen vorkommen können.
Würde ich in einem Produktivsystem machen.

Das musst du schon beim Entwickeln so machen, sonst siehst du ja eventuelle Fehler erst im Produtivbetrieb.

Dann wäre Gruppieren über den Spieler und Zählen der Spiele ein Weg zur Lösung. Dazu noch die Anzahl der Spiele zählen.
Geht es ein bißchen konkreter?

Du brauchst auf alle Fälle Wissen über Joins, falls du das noch nicht hast. Einen Join brauchst du, wenn zum Ergebnis noch Daten zu den Spielern oder Spielen hinzukommen sollen. Für das reine Zählen reicht die m:n-Tabelle, aber dann hast du nur die Spieler-IDs und eine Summe im Ergebnis.

SELECT spieler_id COUNT(*) FROM m_n_tabelle GROUP BY spieler_id

Wenn das Datum eine Rolle spielt, dann muss da jedoch noch die Spiele-Tabelle gejoint werden, um ungewünschte Spiele ausschließen zu können. Alternativen zu Joins können allerdings auch (correlated) Subquerys sein.

dedlfix.

[Stefan]

Hi dedlfix,

ich habe inzwischen die (nein besser: eine) Query erstellt, die mir aber das Ergebnis nicht zeigt, was ich erwarte.

SELECT Name FROM spieler
LEFT JOIN m_n_tabelle ON spieler.SID = m_n_tabelle.SID
WHERE
( (SELECT COUNT(*) FROM spiele) = (SELECT spieler_id COUNT(*) FROM m_n_tabelle GROUP BY spieler_id) )

Stefan

[dedlfix]

Tach!

ich habe inzwischen die (nein besser: eine) Query erstellt, die mir aber das Ergebnis nicht zeigt, was ich erwarte.

SELECT Name FROM spieler
LEFT JOIN m_n_tabelle ON spieler.SID = m_n_tabelle.SID
WHERE
( (SELECT COUNT(*) FROM spiele) = (SELECT spieler_id COUNT(*) FROM m_n_tabelle GROUP BY spieler_id) )

Nehmen wir erstmal das WHERE:

( (SELECT COUNT(*) FROM spiele) = (SELECT spieler_id COUNT(*) FROM m_n_tabelle GROUP BY spieler_id) )

Links gibt es genau ein Ergebnis, rechts gibt es je eines Pro Spieler, also eine Ergebnismenge > 1 (wenn man sich mal das spieler_id vor dem COUNT(*) wegdenkt). Eins mit mehreren zu vergleichen ist nicht besonders sinnvoll. Sollte das nicht auch zu einem Fehler führen?

Punkt zwei ist, dass du keine Verbindung zwischen den Subquerys im WHERE und dem Rest hast. Die linke braucht keine, aber die rechte soll zu jedem einzelnen Spieler aus der Haupt-Query die Anzahl liefern. Also brauchst du da kein GROUP BY sondern ein WHERE spieler_id gleich der id vom Spieler der Hauptquery ist.

Der Left Join ist auch nicht notwendig, wenn du mit correlated Subquery arbeitest (vorausgesetzt, du macht die Korrelation noch rein). Wenn du mit dem Join arbeiten willst, dann braucht es das GROUP BY aber direkt und nicht in einer Subquery. Ungeachtet davon brauchst du das COUNT(*) FROM Spiele weiterhin als Subquery. Und da es mit dem gruppierten COUNT der Spiele pro Spieler zusammenarbeiten soll, kann es nicht im WHERE stehen (das vor dem GROUP BY ausgewertet wird), sondern muss "weiter nach hinten" ins HAVING.

Da wäre also die eine Lösungsmöglichkeit:

SELECT Name FROM spieler s
WHERE (SELECT COUNT(*) FROM spiele) = (SELECT COUNT(*) FROM m_n_tabelle m WHERE s.SID = m.spieler_id)

Die beiden Aliasse können auch wegfallen, wenn die Spaltennamen eindeutig sind. (Du bist bei der Benennung in deinem Beispiel-Code nicht konsequent gewesen. Beim Join ist es SID, in der Subquery spieler_id.)

Und nun noch die zweite:

SELECT spieler.SID, COUNT(*) anzahl FROM spieler
LEFT JOIN m_n_tabelle ON spieler.SID = m_n_tabelle.SID
-- bei Bedarf noch WHERE auf irgendwelche anderen Bedingungen
GROUP BY spieler.SID
HAVING (SELECT COUNT(*) FROM spiele) = anzahl

Andere DBMSe erlauben in der SELECT-Klausel neben dem COUNT nur das spieler.SID, nach dem auch gruppiert wurde. MySQL erlaubt hingegen auch die Angabe weiterer Felder. Du wolltest ja eigentlich noch andere Spielerdaten, zum Beispiel den Namen haben. In diesem Fall ist der Name und alle anderen Spielerdaten durch die Gruppierung auf Spieler-ID eindeutig und kann unter MySQL problemlos ins SELECT geschrieben werden. Wenn durch eine Gruppierungsbedingung unterschiedliche Daten in eine Gruppe gelangen können, so ergibt ein Selektieren darauf einen zufälligen Wert aus der Anzahl der möglichen. Hier wäre das der Fall, wenn du von der m_n_tabelle ein Feld selektieren würdest. Da gibts zwar vermutlich nur noch die Spiel-ID, aber das sind pro Gruppe ja mehrere unterschiedliche Werte, und das würde dann nicht eindeutig enden. Abgesehen davon brauchst du ja die Spiel-ID nicht im Ergebnis. Also stellt das hier kein Problem dar.

dedlfix.

[Stefan]

Tach!

N'abend.

Danke für die lange Erklärung, auch wenn ich nicht alles sofort durchschaue.

Nehmen wir erstmal das WHERE:

( (SELECT COUNT(*) FROM spiele) = (SELECT spieler_id COUNT(*) FROM m_n_tabelle GROUP BY spieler_id) )

Links gibt es genau ein Ergebnis, rechts gibt es je eines Pro Spieler, also eine Ergebnismenge > 1 (wenn man sich mal das spieler_id vor dem COUNT(*) wegdenkt). Eins mit mehreren zu vergleichen ist nicht besonders sinnvoll.

Ja, das stimmt.

Aber warum tuts dann das hier nicht?

SELECT Name FROM spieler
WHERE SID =
( (SELECT COUNT(*) FROM m_n_tabelle) = (SELECT COUNT(*) AS Anzahl FROM spiele GROUP BY SID ORDER BY Anzahl DESC LIMIT 1) )

Da wäre also die eine Lösungsmöglichkeit:

SELECT Name FROM spieler s
WHERE (SELECT COUNT(*) FROM spiele) = (SELECT COUNT(*) FROM m_n_tabelle m WHERE s.SID = m.spieler_id)

Da kommt das gesuchte Ergebnis raus. Nicht schlecht...

Und nun noch die zweite:

SELECT spieler.SID, COUNT(*) anzahl FROM spieler
LEFT JOIN m_n_tabelle ON spieler.SID = m_n_tabelle.SID
-- bei Bedarf noch WHERE auf irgendwelche anderen Bedingungen
GROUP BY spieler.SID
HAVING (SELECT COUNT(*) FROM spiele) = anzahl

Passt ebenso!

Hut ab und Dank, dedlfix. Ich fand (und finde) das nicht wirklich einfach.

Kannst Du mir noch sagen, warum mein obiges Beispiel nicht funktioniert?

Stefan

[dedlfix]

Tach!

Aber warum tuts dann das hier nicht?

SELECT Name FROM spieler
WHERE SID =
( (SELECT COUNT(*) FROM m_n_tabelle) = (SELECT COUNT(*) AS Anzahl FROM spiele GROUP BY SID ORDER BY Anzahl DESC LIMIT 1) )

Die linke Subquery liefert eine Zahl, die Anzahl der Einträge in der m:n-Tabelle, also eigentlich alle vorhandenen Spieler-Spiel-Zuordnungen. Dieser Wert wäre jedoch nicht nützlich für die Lösung deines Problems. Angenommen du hast da stattdessen die Spiele-Tabelle, dann würde das einen Sinn ergeben, hilft dir aber auch nicht weiter, wie ich gleich noch erläutern werde. Die rechte Subquery sollte eigentlich auf die m:n-Tabelle laufen. Du hast da wohl die Bezeichnungen verdreht. Aber auch die richtige Benennung bringt dich nicht voran. Das Ergebnis ist jedenfalls die Anzahl der Spiele des Spielers, der am meisten an allen Spielen teilgenommen hat. Wenn er keins ausgelassen hat, ist das Ergebnis gleich der linken Query, ansonsten ist es ungleich. Bis jetzt hast du ein boolesches Ergebnis vorliegen, weil du etwas miteinander verglichen hast. In MySQL wird das zu 1 oder 0. Und nun vergleichst du die SID damit. Du bekommst also den Namen des Spielers mit der ID 1 (so vorhanden), wenn mindestens einer der Spieler an allen Spielen teilgenommen hat, ansonsten eine leere Menge.

dedlfix.

[Stefan]

Die linke Subquery liefert eine Zahl, die Anzahl der Einträge in der m:n-Tabelle, also eigentlich alle vorhandenen Spieler-Spiel-Zuordnungen. Dieser Wert wäre jedoch nicht nützlich für die Lösung deines Problems. Angenommen du hast da stattdessen die Spiele-Tabelle, dann würde das einen Sinn ergeben, hilft dir aber auch nicht weiter, wie ich gleich noch erläutern werde. Die rechte Subquery sollte eigentlich auf die m:n-Tabelle laufen. Du hast da wohl die Bezeichnungen verdreht. Aber auch die richtige Benennung bringt dich nicht voran. Das Ergebnis ist jedenfalls die Anzahl der Spiele des Spielers, der am meisten an allen Spielen teilgenommen hat. Wenn er keins ausgelassen hat, ist das Ergebnis gleich der linken Query, ansonsten ist es ungleich. Bis jetzt hast du ein boolesches Ergebnis vorliegen, weil du etwas miteinander verglichen hast. In MySQL wird das zu 1 oder 0. Und nun vergleichst du die SID damit. Du bekommst also den Namen des Spielers mit der ID 1 (so vorhanden), wenn mindestens einer der Spieler an allen Spielen teilgenommen hat, ansonsten eine leere Menge.

Hi dedlfix,

super erklärt und alle Annahmen stimmen.
1. Habe ich die Bezeichnungen verdreht, das war flüchtigkeitsfehlerhafterweise so.
2. Bekomme ich tatsächlich den Namen des Spielers mit der SID 1 heraus (weil ich eigens einen Spieler angelegt hatte, der an allen Spielen teilnahm --> der hatte allerdings eigentlich die SID 2 --> ich weiß nun, warum mir dieser nicht angezeigt wurde).

Dank Dir

Stefan

[Stefan]

Hi nochmal,

Und nun noch die zweite:

SELECT spieler.SID, COUNT(*) anzahl FROM spieler
LEFT JOIN m_n_tabelle ON spieler.SID = m_n_tabelle.SID
-- bei Bedarf noch WHERE auf irgendwelche anderen Bedingungen
GROUP BY spieler.SID
HAVING (SELECT COUNT(*) FROM spiele) = anzahl

kannst Du mir bitte nochmal diese Query erklären? Ich verstehe hier den ersten count(*) auf die spieler-Tabelle nicht ganz. Warum nimmt die Query tatsächlich nicht die Anzahl der Spieler sondern die gruppierten Werte?

Stefan

[dedlfix]

Tach!

SELECT spieler.SID, COUNT(*) anzahl FROM spieler
LEFT JOIN m_n_tabelle ON spieler.SID = m_n_tabelle.SID
-- bei Bedarf noch WHERE auf irgendwelche anderen Bedingungen
GROUP BY spieler.SID
HAVING (SELECT COUNT(*) FROM spiele) = anzahl

kannst Du mir bitte nochmal diese Query erklären? Ich verstehe hier den ersten count(*) auf die spieler-Tabelle nicht ganz. Warum nimmt die Query tatsächlich nicht die Anzahl der Spieler sondern die gruppierten Werte?

Das ist, warum man ein GROUP BY verwendet, weil das dann Gruppen bildet und die Aggregatfunktionen ihre Ergebnisse jeweils aus den Datensätzen dieser Gruppen ziehen.

dedlfix.