title: How do i use COUNT with JOIN SQL 3 tables
body: <p>I have 3 tables.</p>
  
  <p>First table is Battles with columns:</p>
  
  <pre><code>--battleId(primary)--gameId(foreign)--prize--
  </code></pre>
  
  <p>Second table is Games with columns:</p>
  
  <pre><code>--gameId(primary)--gameName--
  </code></pre>
  
  <p>Thrid table is BattleParticipants with columns:</p>
  
  <pre><code>--battleParticipantsId(primary)--userId(foreign)--battleId(foreign)--score--
  </code></pre>
  
  <p>First i wrote this query to join Battles and Games and get battleId,prize and gameName data:</p>
  
  <pre><code>SELECT
      battles.battleId
    , battles.prize
    , games.gameName 
  FROM
    battles 
  JOIN 
    games 
  ON 
    battles.gameId = games.gameId
  </code></pre>
  
  <p>This query returs  <code>battleId,prize,gameName</code> data of <strong>each</strong> battle.</p>
  
  <p>I want to extend this query to get <strong>number of participants in each battle</strong>(which means rows in BattleParticipant table with Battles.battleId = BattleParticipants.battleId )
   by combining it somehow with BattleParticipants table.</p>
  
  <p>So result should be:
  battleId,prize,gameName,numberOfParticipantInBattle</p>
  
  <p>Example Tables and result:</p>
  
  <p>Battles:</p>
  
  <pre><code> --battleId(primary)--gameId(foreign)--prize--
        asd123               1               100
        asd1234              2               200
        asd1235              1               300
  </code></pre>
  
  <p>Games:</p>
  
  <pre><code>  --gameId(primary)--gameName--
             1             Some Game
             2             Another Game
  </code></pre>
  
  <p>BattleParticipants:</p>
  
  <pre><code>   --battleParticipantsId(primary)--userId(foreign)--battleId(foreign)--score--
                   1                     qwedqweqw       asd123             100
                   2                     qweqwe         asd1234             200
                   3                     qweadgjhg      asd123              400
                   4                       asd           asd123             500
  </code></pre>
  
  <p>Expected result:</p>
  
  <pre><code>[ RowDataPacket {
      battleId: 'asd123',
      prize: 100,
      gameName: 'Some Game',
      numberOfParticipantInBattle: 3 },
    RowDataPacket {
      battleId: 'asd1234',
      prize: 200,
      gameName: 'Some Game',
      numberOfParticipantInBattle: 1 },
    RowDataPacket {
      battleId: 'asd1235',
      prize: 300,
      gameName: 'Some Game',
      numberOfParticipantInBattle: 0 }
   ]
  </code></pre>
  
  <p>This is what i got if i try given answer by @Gordon Linoff:</p>
  
  <pre><code>[ RowDataPacket {
      battleId: 'asd123',
      prize: 100,
      gameName: 'Some Game',
      numberOfParticipantInBattle: 3 },
    RowDataPacket {
      battleId: 'asd1234',
      prize: 200,
      gameName: 'Some Game',
      numberOfParticipantInBattle: 1 },
    RowDataPacket {
      battleId: 'asd1235',
      prize: 300,
      gameName: 'Some Game',
      numberOfParticipantInBattle: 0 },
    RowDataPacket {
      battleId: 'asd123',
      prize: 100,
      gameName: 'Another Game',
      numberOfParticipantInBattle: 3 },
    RowDataPacket {
      battleId: 'asd1234',
      prize: 200,
      gameName: 'Another Game',
      numberOfParticipantInBattle: 1 },
    RowDataPacket {
      battleId: 'asd1235',
      prize: 300,
      gameName: 'Another Game',
      numberOfParticipantInBattle: 0 } ]
  </code></pre>
  
  <p>is there any way to remove duplicates or should i do it manually</p>