How do i use COUNT with JOIN SQL 3 tables

I have 3 tables.

First table is Battles with columns:

--battleId(primary)--gameId(foreign)--prize--

Second table is Games with columns:

--gameId(primary)--gameName--

Thrid table is BattleParticipants with columns:

--battleParticipantsId(primary)--userId(foreign)--battleId(foreign)--score--

First i wrote this query to join Battles and Games and get battleId,prize and gameName data:

SELECT
    battles.battleId
  , battles.prize
  , games.gameName 
FROM
  battles 
JOIN 
  games 
ON 
  battles.gameId = games.gameId

This query returs battleId,prize,gameName data of each battle.

I want to extend this query to get number of participants in each battle(which means rows in BattleParticipant table with Battles.battleId = BattleParticipants.battleId ) by combining it somehow with BattleParticipants table.

So result should be: battleId,prize,gameName,numberOfParticipantInBattle

Example Tables and result:

Battles:

 --battleId(primary)--gameId(foreign)--prize--
      asd123               1               100
      asd1234              2               200
      asd1235              1               300

Games:

  --gameId(primary)--gameName--
           1             Some Game
           2             Another Game

BattleParticipants:

   --battleParticipantsId(primary)--userId(foreign)--battleId(foreign)--score--
                 1                     qwedqweqw       asd123             100
                 2                     qweqwe         asd1234             200
                 3                     qweadgjhg      asd123              400
                 4                       asd           asd123             500

Expected result:

[ 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 }
 ]

This is what i got if i try given answer by @Gordon Linoff:

[ 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 } ]

is there any way to remove duplicates or should i do it manually

Answer

I think you just need an extra JOIN and GROUP BY:

SELECT b.battleId, b.prize, g.gameName,
       COUNT(*) as numberOfParticipantInBattle
FROM battles b JOIN
     games g
     ON b.gameId = b.gameId JOIN
     BattleParticipants bp
     ON b.battleId = bp.battleId
GROUP BY b.battleId, b.prize, g.gameName;

If some battles might have no participants, and you want to include them, then use a LEFT JOIN:

SELECT b.battleId, b.prize, g.gameName,
       COUNT(bp.battleId) as numberOfParticipantInBattle
FROM battles b JOIN
     games g
     ON b.gameId = b.gameId LEFT JOIN
     BattleParticipants bp
     ON b.battleId = bp.battleId
GROUP BY b.battleId, b.prize, g.gameName;

Advertisement

Answer