Is count() being used correctly for my query?

Question

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 1

I can only speculate on what your data really looks like. However, it is doubtful that this expression:

having count(Bowler_Scores.WonGame) = 1;

does what you want. This counts the number of non-NULL values. Presumably, WonGame as some value such as "1" or "W" for the winner. If the value were 1, then the correct expression would be:

having sum(Bowler_Scores.WonGame) = 1

This is just speculation though without a better description of your data.

EDIT:

Based on the comment:

having sum(convert(int, Bowler_Scores.WonGame)) = 1

Answer 2

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.