Query für Fußballstatistik

[pubmaster]

Hallo zusammen!

Ich grüble nun seit einigen Tagen an einem mySQL-Problem und habe mich nun hier angemeldet, um etwas erfahrenere User nach möglichen Lösungen zu fragen.

Zum Problem:
Ich führe in einer mySQL-Datenbank Statistik über die Ergebnisse einer Fußballliga (über mehrere Jahre). Die Datenbank besteht dabei einmal aus der Tabelle "teams", die für jedes Team, das in der Liga in den Jahren seit Beginn der Statistik in der Liga gespielt hat, einen Eintrag mit ID und Namen enthält:

CREATE TABLE IF NOT EXISTS `teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

Die zweite Tabelle "matches" enthält in jeder Zeile Informationen eine Partie, bestehend aus der ID (Primary Key), den IDs der Heim- und Auswärtsmannschaften, den erzielten Toren der Heim- und Auswärtsmannschaften und der Saison, in der die Partie stattgefunden hat:

CREATE TABLE IF NOT EXISTS `matches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `team1_id` int(11) NOT NULL,
  `team2_id` int(11) NOT NULL,
  `team1_score` int(11) NOT NULL,
  `team2_score` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

Nun will ich aus der Tabelle "matches" folgende Informationen über jedes Team ermitteln:
- Partien gesamt gespielt
- davon gewonnen / verloren / unentschieden
- Tore gesamt (geschossen / bekommen)

Folgenden Query für die Zahl der gespielten Partien habe ich dabei bis jetzt erstellt:

SELECT teams.name, COUNT(*) as num_matches FROM matches INNER JOIN teams ON team1_id = teams.id GROUP BY teams.id ORDER BY num_matches DESC

Dieser gibt folgendes zurück:

##############################
#   name   #   num_matches   #
##############################
#  FC abc  #       112       #
#  FC xyz  #       65        #
#  FC 123  #       48        #
##############################

Dabei werden eben leider nur die Heimspiele jedes Vereins berücksichtigt, da im Query nur die "team1_id" verglichen wird, und an diesem Punkt komme ich nicht weiter.

Nun meine Fragen:
- Wie müsste ich meinen Query nun umbauen, damit die Struktur des Ergebnisses gleich bleibt, jedoch die Auswärtsspiele ("team2_id"s) mit berücksichtigt werden?
- Wie löse ich das (beinahe selbe) Problem für die Torstatistik?

Vielen Dank für die Hilfe!
MfG,
Pubmaster

[dibo33]

Mache einen weiteren Join auf teams allerdings diesmal mit team2_id = teams.id, hierzu musst du jedoch Alias-Bezeichner einsetzen.