Is count() being used correctly for my query?

Find all teams that only won 1 game in tourney #3 (1 column, 4 rows)

My thought process to create this query is that I need to count (WonGame)s for each Team. And that that number cannot be has to equal 1. But When I run my query I get no results (I should get 4 teams).

Experimenting with my query I changed the equals to a greater than and that returned 8 results. So I don’t understand why equals 1 returns no results.

Also I checked my Data and there is indeed 4 teams that one only one game during Tournament #3.

select Teams.TeamName
from Teams
join Bowlers on Teams.TeamID = Bowlers.TeamID
join Bowler_Scores on Bowlers.BowlerID = Bowler_Scores.BowlerID
join Match_Games on Bowler_Scores.GameNumber = Match_Games.GameNumber
join Tourney_Matches on Match_Games.MatchID = Tourney_Matches.MatchID
where Tourney_Matches.TourneyID = 3
group by Teams.TeamName
having count(Bowler_Scores.WonGame) = 1;

Bowling League DB Structure

Bowling League Data

Answer

The diagram seems to indicate that the relationship between Match_Games and Bowler_Scores is on BOTH of MatchID and GameNumber

If you change your JOIN conditions to be both columns

join Match_Games on Bowler_Scores.GameNumber = Match_Games.GameNumber and Bowler_Scores.MatchID = Match_Games.MatchID

Then you might get the required answer.

Advertisement

Answer