Is this query possible?

Question

Sorry in advance for the massive queries. I've been trying and cannot for the life of me get this query to work. It's adding wins and losses to both users when two logged in users are playing against each other. (It's rock paper scissors). I can make it work for a single user, but when I try to gather a 'stats-table' summing over users, I get the duplications.

Here is my abbreviated-for-the-sake-of-brevity schema

create table rps_user (
    user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(255),
    PRIMARY KEY (user_id),
    UNIQUE (username)
);

CREATE TABLE rps_session (
  session_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  player1_user_id INT UNSIGNED DEFAULT NULL,
  player2_user_id INT UNSIGNED DEFAULT NULL,
  connected BOOLEAN DEFAULT 0,
  PRIMARY KEY (session_id)
);

CREATE TABLE rps_game (
  game_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  game_number INT UNSIGNED DEFAULT NULL,
  session_id INT UNSIGNED NOT NULL,
  player1_choice ENUM('ROCK','PAPER','SCISSORS') DEFAULT NULL,
  player2_choice ENUM('ROCK','PAPER','SCISSORS') DEFAULT NULL,
  PRIMARY KEY (game_id)
);

And here is my query that works

SELECT IF((player1_choice + 1) % 3 + 1 = player2_choice + 0 AND player1_user_id = rps_user.user_id OR
        (player2_choice + 1) % 3 + 1 = player1_choice + 0 AND player2_user_id = rps_user.user_id, 1, 0) AS win, 
    IF(player1_choice = player2_choice, 1, 0) as tie, 
    IF(player1_choice % 3 + 1 = player2_choice + 0 AND player1_user_id = rps_user.user_id OR
        player2_choice % 3 + 1 = player1_choice + 0 AND player2_user_id = rps_user.user_id, 1, 0) AS loss
    FROM rps_game INNER JOIN rps_session USING (session_id)
    INNER JOIN rps_user ON rps_session.player1_user_id = rps_user.user_id OR rps_session.player2_user_id = rps_user.user_id
    WHERE player1_choice IS NOT NULL AND player2_choice IS NOT NULL and rps_user.user_id = ?

And the one that doesn't

SELECT username, SUM(IF((player1_choice + 1) % 3 + 1 = player2_choice + 0 AND player1_user_id = rps_user.user_id OR
        (player2_choice + 1) % 3 + 1 = player1_choice + 0 AND player2_user_id = rps_user.user_id, 1, 0)) AS wins, 
    SUM(IF(player1_choice = player2_choice, 1, 0)) AS ties,
    SUM(IF(player1_choice % 3 + 1 = player2_choice + 0 AND player1_user_id = rps_user.user_id OR
        player2_choice % 3 + 1 = player1_choice + 0 AND player2_user_id = rps_user.user_id, 1, 0)) AS losses
    FROM rps_game INNER JOIN rps_session USING (session_id)
    INNER JOIN rps_user ON rps_session.player1_user_id = rps_user.user_id OR rps_session.player2_user_id = rps_user.user_id
    WHERE player1_choice IS NOT NULL AND player2_choice IS NOT NULL
    GROUP BY user_id ORDER BY wins DESC

Good luck and thank you!

Answer 1

At present, your table design isn't properly normalised - you will always have two different players associated with each rps_session and rps_game record. Given my choice, I would normalise these, so that there is a link table between each of them and the rps_user table.

Having said that, there's a strong chance you are not able to do that, so there is a simple answer: simply divide the wins, losses and ties figure from the second query by 2. This should always work, as for every game where one user is user1, another user is user2 - which is why you are seeing double values.

NB. If the relationships between the three tables in your actual system are more complicated than what you've presented here (so that you can't rely on there always being two users for every game), this solution will not be reliable.