CODEWITHSUNDEEP
mysqlsql
Manuel Pepe
Manuel Pepe

Reputation: 93

Making a score table (hard)

I need your help.

I have a database with a schema like this:

teams:

matchs:

players:

goals:

and I need to do (among other things) this:

Show by team: Played matchs, winned matchs and drawn matchs (in different columns)

I have something like this:

SELECT t.name,
SUM(CASE t.id WHEN m.id_local_team THEN 1 WHEN m.id_visit_team THEN 1 ELSE 0 END) AS played,
SUM(CASE (CASE m.winner
              WHEN 'local' THEN m.id_local_team
              WHEN 'visit' THEN m.id_visit_team 
              ELSE NULL END) 
        WHEN t.id THEN 1 
        ELSE 0 END) AS winned,
SUM(CASE m.winner WHEN 'draw' THEN 1 ELSE 0 END) AS drawn
FROM teams AS t
INNER JOIN matchs AS m
    ON (t.id = m.id_local_team OR t.id = m.id_visit_team)
GROUP BY t.name;

But that is giving me wrong results. Like, there are 8 matchs total, and the (4) teams are returning 12, 9, or 10 matchs winned (total of 43 matchs), a total of 16 winned matchs and a total of 10 drawn matchs. All above of 8.

What is happening??

In the full query I also have two more inner joins:

INNER JOIN players AS p
    ON (p.id_team = t.id)
INNER JOIN goals AS g
    ON (p.id = g.id_jugador)

I don't think it has nothing to do with these last ones. I know (think?) that i didn't do the matchs join correctly.

I appreciate if you have made it this far into the post!

The real schema is in spanish actually in Spanish (sorry for that guys) but here is all the magic:

SCHEMA

| equipos | CREATE TABLE `equipos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nombre` varchar(180) NOT NULL,
  `f_fundacion` date DEFAULT NULL,
  PRIMARY KEY (`id`)
)
| partidos | CREATE TABLE `partidos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fecha` datetime DEFAULT NULL,
  `id_equipo_local` int(11) DEFAULT NULL,
  `id_equipo_visitante` int(11) DEFAULT NULL,
  `ganador` enum('local','visitante','empate') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_partidos_equipos_1` (`id_equipo_local`),
  KEY `fk_partidos_equipos_2` (`id_equipo_visitante`),
  CONSTRAINT `fk_partidos_equipos_1` FOREIGN KEY (`id_equipo_local`) REFERENCES `equipos` (`id`),
  CONSTRAINT `fk_partidos_equipos_2` FOREIGN KEY (`id_equipo_visitante`) REFERENCES `equipos` (`id`)
)

QUERY

SELECT e.nombre, 
    SUM(CASE e.id WHEN p.id_equipo_visitante THEN 1 WHEN p.id_equipo_local THEN 1 ELSE 0 END) AS jugados, 
    SUM(CASE (CASE ganador 
              WHEN 'local' THEN p.id_equipo_local 
              WHEN 'visitante' THEN p.id_equipo_visitante 
              ELSE NULL END) 
        WHEN e.id THEN 1 
        ELSE 0 END) AS ganados,
    SUM(CASE ganador WHEN 'empate' THEN 1 ELSE 0 END) AS empatados,
    SUM(CASE (CASE ganador 
              WHEN 'local' THEN p.id_equipo_local 
              WHEN 'visitante' THEN p.id_equipo_visitante 
              ELSE NULL END) 
        WHEN e.id THEN 1 
        ELSE 0 END) * 3 + SUM(CASE ganador WHEN 'empate' THEN 1 ELSE 0 END) AS puntos,
    COUNT(DISTINCT g.id) AS goles_a_favor
    FROM equipos AS e
    INNER JOIN partidos AS p
        ON (e.id = p.id_equipo_visitante OR e.id = p.id_equipo_local)
    INNER JOIN jugadores AS j
        ON (j.id_equipo = e.id)
    INNER JOIN goles AS g
        ON (j.id = g.id_jugador)
    GROUP BY e.nombre;

RESULTS

+----------------------------------+---------+---------+-----------+--------+---------------+
| nombre                           | jugados | ganados | empatados | puntos | goles_a_favor |
+----------------------------------+---------+---------+-----------+--------+---------------+
| Club Atlético All Boys           |      12 |       6 |         3 |     21 |             3 |
| Club Atlético Chacarita Juniors  |      12 |       3 |         0 |      9 |             3 |
| Club Atlético Ferrocarril Oeste  |       9 |       3 |         3 |     12 |             3 |
| Club Atlético Tucumán            |      10 |       4 |         4 |     16 |             2 |
+----------------------------------+---------+---------+-----------+--------+---------------+

Upvotes: 0

Views: 96

Answers (2)

Aleksi Yrttiaho
Aleksi Yrttiaho

Reputation: 8446

You say that the full query contains joins to each goal made in a given match. This would lead to a situation where the each match is counted N times where N is the number of goals in the match. So for a 0-0 draw the match won't be counted at all, for a 1-0 match the match is counted once for the home team and zero times for the visiting team and 1-2 once for the home team and twice for the visiting team.

To check the number of goals in favor you should first calculate the the goal balance per match using a subquery or a view and then join with that. Then you won't have to problem caused by joining with the player-table.

Upvotes: 1

Jesse Williams
Jesse Williams

Reputation: 662

It does look like the Matchs JOIN is a problem. So you are matching every match at least twice, once for the home team and once for the visiting team, but that doesn't quite explain 43 matches being displayed. Would it be possible to maybe see the full set of results? Sometimes SQL stuff can get touch to debug without access to the tables themselves, but at least seeing the results and what are duplicated might help.

You may want to join only on the winning teams - that should cut half of it out. Actually, since you seem to be trying to get match information, I would SELECT data FROM matches rather than teams. Selecting FROM the table that will limit your total selected rows is always your best bet, then JOIN from there.

Upvotes: 0

Related Questions