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?