MYSQL search for each name in 2 columns and add values associated for each player

Question

I have a database "game_results".

It contains the following:

PlayerID (int not null primary key auto increment)
player1 (varchar)
p1destroyed (int)
player2 (varchar)
p2destroyed (int)
winner (varchar)

It's not the most efficient database (multiple repeats of data in several columns), but it's meant to be a simple way of tracking game results.

After each game, the players input their names, the point amount of ships they killed of the opposing player and then who won.

I'm trying to get the data from both players and add up the for and against points destroyed for each player.

I know this is where the poor design has bitten me, but I'd rather try and figure this out for other things I have planned.

I want to be able to get a result set that gets each unique player with both the amount of points they have destroyed and the amount they have had destroyed by their opponents.

I have figured out how to do it in one way:

SELECT
player1, SUM(p1destroyed) AS Fmov, SUM(p2destroyed) AS Amov 
FROM game_results
GROUP BY player1;

But I'm stuck getting the unique values from both p1 and p2 to merge.

I'm thinking a union is probably the right place to look, but I just can't seem to make it work.

Any ideas please?

Answer 1

A UNION would work like this:

SELECT player, SUM(Fmov), SUM(Amov) FROM 
  (SELECT player1 AS player, SUM(p1destroyed) AS Fmov, SUM(p2destroyed) AS Amov 
     FROM game_results
     GROUP BY player1
  UNION
  SELECT player2 AS player, SUM(p2destroyed) AS Fmov, SUM(p1destroyed) AS Amov
     FROM game_results
     GROUP BY player2) AS temp
  GROUP BY player

Another option would be a full join, but MySQL doesn't support that out of the box so it's probably longer to write.

Answer 2

Glorfindel's answer is good within the parameters of your question, but I'll talk a bit about how else you could have designed it, in case that's helpful.

Basically, having a single table with player1 and player2 isn't great because the two players have the same relationship to the game so it should be represented the same way. If it's important to keep track of which one was #1 and which was #2, then it's probably better to think of that as an attribute of the player's membership in the game.

CREATE TABLE `player` (`id` PRIMARY KEY, ...);

CREATE TABLE `game_results` (`id` PRIMARY KEY, ...);

CREATE TABLE `game_player` (
  `game_id`,
  `player_id`,
  `destroyed`,
  PRIMARY KEY (`game_id`, `player_id`),
  FOREIGN KEY (`game_id`) REFERENCES `game_results` (`id`),
  FOREIGN KEY (`player_id`) REFERENCES `player` (`id`),
  ...
);

Some open questions remain from there.

• Where should the results be recorded?
  • You could just have a nullable foreign key winner from game_results to game_player (id).
  • You could have an attribute result of game_player. This could be an enum if you're in MySQL 5.7, or a foreign key to a small table of possible results. I'd prefer this because it would better accommodate draws and annulments.
• The join-table design naturally allows for games with more than two players. Whether or not that's good depends on the situation. Assuming we want to limit the number of players, how would we do that?
  • Add another enum or foreign key to an enum table representing the idea of "player 1" and "player 2". Add a UNIQUE KEY game_player (game_id, player_number). This is what I would suggest.
  • Add a FOREIGN KEY game_player(partner) REFERENCES game_player(id). This would work for your current situation, but isn't an open-ended solution.
  • You can't use a CHECK constraint in MySQL. This is one of the reasons I dislike MySQL. Here's the reference.

In any case, the final query you're trying to build would look something like

SELECT gp1.player_id, SUM(gp1.destroyed) AS Fmov, SUM(gp2.destroyed) AS Fmov 
  FROM `game_player` gp1
  [LEFT | INNER] JOIN `game_player` gp2
    ON gp1.game_id = gp2.game_id
      AND gp1.player_id <> gp2.player_id
  [WHERE gp1.result in (`win`, `loose`, `tie`)]
  GROUP BY gp1.player_id;