title: Can't display matches won, matches played and matches drawed by each player
body: <pre><code>CREATE TABLE matches (
    match_id BIGSERIAL PRIMARY KEY,
    tournamentid INTEGER,
    player1_id INTEGER,
    player2_id INTEGER CHECK (player1_id &lt; player2_id),
    result INTEGER CHECK (result IN (0, 1, 2)),
    FOREIGN KEY(tournamentid, player1_id) REFERENCES enroll(tournament, player_id),
    FOREIGN KEY(tournamentid, player2_id) REFERENCES enroll(tournament, player_id),
    UNIQUE(tournamentid, player1_id, player2_id)
  );   
  
  CREATE VIEW player_standings AS (
  SELECT tournaments.tournament_id,
       tournaments.tournament_name,
       enroll.player_id,
       players.name,
       CASE
            WHEN matches.result = 1 THEN COUNT(matches.player1_id)
            WHEN matches.result = 2 THEN COUNT(matches.player2_id)
            END AS wins,
  
       COUNT(enroll.player_id IN (matches.player1_id, matches.player2_id)) AS match_played
  
  FROM players
    INNER JOIN enroll ON enroll.player_id = players.id
    INNER JOIN tournaments ON tournaments.tournament_id = enroll.tournament
    LEFT JOIN matches ON (matches.player1_id = enroll.player_id) or
         (matches.player2_id = enroll.player_id)
  
  GROUP BY tournaments.tournament_id, tournaments.tournament_name,   
      enroll.player_id, players.name, matches.result
  ORDER BY tournaments.tournament_id, wins DESC
  );
  </code></pre>
  
  <p>I can't seem to get the number of matches won correctly. I think the problem has to do with the left join with the matches table. </p>
  
  <p>The idea is to read from the column <code>result</code> from the matches table where the results are interpreted as:</p>
  
  <pre><code>0 = draw
  1 = player1 won
  2 = player2 won 
  </code></pre>
  
  <p>With the current schema, I can get the number of matches played for each players correctly but not the number of matches played. </p>
  
  <p>I'm also hoping to add the number of tied matches without having to break up into multiple views or tables, if possible. any advice?</p>