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
Advertisement
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;