Skip to content
Advertisement

How do i use COUNT with JOIN SQL 3 tables

I have 3 tables.

First table is Battles with columns:

Second table is Games with columns:

Thrid table is BattleParticipants with columns:

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

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:

Games:

BattleParticipants:

Expected result:

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

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:

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement