How do I JOIN a third table in my SQL statement which returns a COUNT without losing the 0 count items?

Question

A continuation from this question I need a SQL statement that returns the number rows in a table with a specific value.

We left off with a SQL statement as follows...

   SELECT t.teamid,
          t.teamname,
          COALESCE(COUNT(p.playerid), 0) AS playercount,
          t.rosterspots
     FROM TEAMS t
LEFT JOIN PLAYERS p ON p.teamid = t.teamid
 GROUP BY t.teamid, t.teamname, t.rosterspots

I have one more constraint to add. WHAT IF, players need to pass a Medical Exam before they count towards the "playercount"?

I'll introduce the table.

MedicalTests
PlayerId  PassedMedical
1         1
2         0
3         1
4         1

Where "PassedMedical" is a bit (1 = true).

And also add 1 more ROW of data to the Teams table.

TeamId    Team Name
3         Toronto Rapters

This way I have a team with 0 players.

And the expected OUTPUT changes to:

Team Name         PlayerCount
Miami Heat        2
New York Knicks   1
Toronto Rapters   0

since one of the Miami Heat players has not yet passed the medical.

If I add

LEFT JOIN MEDICALTESTS m ON p.PlayerId = m.PlayerId
WHERE m.PassedMedical = 1

To the above statement I lose all the "0" rows?

Thanks,
Justin

Answer 1

LEFT JOIN MEDICALTESTS m ON p.PlayerId = m.PlayerId
WHERE m.PassedMedical = 1

That's contradictory. You are saying it is a left join, so the MEDICALTESTS record need not exist, but then saying the record must in fact exist and have a 1 as PassedMedical. So your left join becomes an inner join in fact, and likewise, since you must have a MEDICALTESTS row, you must also have a PLAYERS row, so that also becomes an inner join.

Try this instead:

INNER JOIN MEDICALTESTS m ON p.PlayerId = m.PlayerId and m.PassedMedical = 1

Not sure how SQL server interprets FROM foo LEFT JOIN bar INNER JOIN baz...you want it to interpret that as FROM foo LEFT JOIN (bar INNER JOIN baz). If that doesn't work, use a subselect instead; don't join, just add:

WHERE p.PlayerId IS NULL or (select PassedMedical from MEDICALTESTS m where m.PlayerId=p.PlayerID) = 1 

Hope this helps despite my lack of familiarity with SQL Server.

Answer 2

Forgive me if I've misunderstood the question, but I don't see the need for the left join onto the players table. We only need the count of players. Why not something like:

SELECT t.teamid,
          t.teamname, 
          (SELECT COUNT(*) FROM players inner join medicaltests on players.playerid = medicaltests.playerid where players.teamid = t.teamid and medicaltests.passedmedical = 1) AS playercount,
          t.rosterspots
     FROM TEAMS t
 GROUP BY t.teamid, t.teamname, t.rosterspots

Answer 3

Why not doing it as a computed column?

SELECT
    t.teamid, 
    t.teamname,
    (SELECT COUNT(*) FROM MEDICALTESTS m WHERE p.PlayerId = m.PlayerId and m.PassedMedical = 1 ) AS PassedMedicalCount

Answer 4

Read this to understand the problem you are having: http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN

Basically by putting the condition in the where clasue you are converting the left join to an inner join. Try this instead:

LEFT JOIN MEDICALTESTS m ON p.PlayerId = m.PlayerId 
and m.PassedMedical = 1