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

MySQLselectunion

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?

Best Answer

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.

Related Question