games sql

schema.sql

CREATE TABLE wins (
    win_id      INT             NOT NULL,
    game_id     INT             NOT NULL,
    winner_id   INT             NOT NULL,
    PRIMARY KEY (win_id)
);

CREATE TABLE games (
    game_id     INT             NOT NULL,
    player_id   INT             NOT NULL,
    color       ENUM('white', 'black') NOT NULL,
    PRIMARY KEY (game_id)
);

# count of all the wins for a player
SELECT COUNT(wins.win_id) FROM wins JOIN games ON wins.winner_id = games.player_id AND games.player_id = $PLAYER

# count of all the games against a player
SELECT game_id, player_id, winner_id, color FROM games JOIN wins ON wins.game_id = games.games_id AND games.player_id = $PLAYER