1

I am trying to use a JOIN ON statement to SELECT two columns, one with the Team_Name and the other with how many times they have won. I want to include all teams, even those who haven't won at all.

This is the JOIN statement that I've been using but it only returns the Team_Name Poki and a WinningTimes of 0 which is incorrect as they've won once

SELECT Team_Name, SUM(Winner) AS WinningTimes 
From Robot_Winner r JOIN Robot_Combat c
ON r.BattleNo = c.BattleNo;



Team_Name    WinningTimes
Poki         0
Create Table Robot_Combat
(
BattleNo VarChar(3) Not null,
Team_Number Int Not null,
Team_Name VarChar(8) Not null,
Bot_Name VarChar(8) Not null,
Primary Key (BattleNo, Bot_Name)
);

Create Table Robot_Winner
(
BattleNo VarChar(3) Not null,
Winner VarChar (10) Not null,
Primary Key (BattleNo),
Foreign Key (BattleNo) References Robot_Combat(BattleNo)
);

This is the data inside each table

Robot_Combat
BattleNo [PK]   Team_Number Team_Name   Bot_Name
B07             1           A1          S1
B07             2           Poki        Pika
B08             1           Phenix      Kka
B08             2           StarWar     R2
B11             1           APT         4869
B11             2           Phenix      RedWin
B12             1           T1          E1
B12             2           S3          Sam5

Insert Into Robot_Combat (BattleNo, Team_Number, Team_Name, BotName)
Values ('B07', 1, 'A1', 'S1');
Insert Into Robot_Combat (BattleNo, Team_Number, Team_Name, BotName)
Values ('B07', 2, 'Poki', 'Pika');
Insert Into Robot_Combat (BattleNo, Team_Number, Team_Name, BotName)
Values ('B08', 1, 'Phenix', 'Kka');
Insert Into Robot_Combat (BattleNo, Team_Number, Team_Name, BotName)
Values ('B08', 2, 'StarWar', 'R2');
Insert Into Robot_Combat (BattleNo, Team_Number, Team_Name, BotName)
Values ('B11', 1, 'APT', '4869');
Insert Into Robot_Combat (BattleNo, Team_Number, Team_Name, BotName)
Values ('B11', 2, 'Phenix', 'RedWin');
Insert Into Robot_Combat (BattleNo, Team_Number, Team_Name, BotName)
Values ('B12', 1, 'T1', 'E1');
Insert Into Robot_Combat (BattleNo, Team_Number, Team_Name, BotName)
Values ('B12', 2, 'S3', 'Sam5');

Robot_Winner
BattleNo [FK][PK]   Winner
B07                 Poki
B08                 Phenix
B11                 Phenix
B12                 S3

Insert Into Robot_Winner(BattleNo, Winner)
Values ('B07','Poki);
Insert Into Robot_Winner(BattleNo, Winner)
Values ('B08','Phenix);
Insert Into Robot_Winner(BattleNo, Winner)
Values ('B11','Phenix);
Insert Into Robot_Winner(BattleNo, Winner)
Values ('B12','S3);
  • When sharing a sample data first check from your side then share.Like the alias name s and the Winner VarChar (3) – James Nov 3 '19 at 4:32
  • Tip: Keep in mind when sharing create... share Insert.. statements too so that community can use to replicate in local faster. It would be kind if you add fiddle sample here – James Nov 3 '19 at 5:07
  • If you need all records from one of joined tables you must use outer joining or correlated subquery. Simple join, which is INNER, cannot solve. – Akina Nov 3 '19 at 6:43
0

If you need Team Name and Winning count why you need a join instead you can get the count in the Robot_Winner right?

There you have the winning team name which is Team_Name and no of games won can be counted by grouping Winner.

So it can be written as below

select 
    Winner as Team_Name,
    count(*) as WinningTimes 
from 
    robot_winner
group by 
    Winner;

Edit:

Since you need all the teams and the no. of count won. You can use left join and select the team_name from robot_combat table.

select
    team.Team_Name as Team_Name,
    count(winner.Winner) as WinningTimes
from
    (select distinct Team_Name from robot_combat) as team
    left join robot_winner as winner on team.Team_Name = winner.Winner
group by 
    Team_Name;

caveat: You need to normalize your table like having a team names in a separate table.

|improve this answer|||||
  • Because I need to include the teams that didn't win. – HKeys34 Nov 3 '19 at 4:32
  • Then you need to explain a bit in detail. So edit your question please – James Nov 3 '19 at 4:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.