Baffled by join results after adding WHERE clause

Question

I'm trying to count the number of clients associated with each health clinic, and I want to show all clinics even if the number of clients is 0. My query works as expected until I throw in a WHERE clause.

Here's a stripped down description of the database tables and query to get to the essence of the problem.

clients table

client_id | health_clinic_id | accepted  
----------------------------------------
1           1                  1  
2           2                  NULL  
3           1                  1  

options_health_clinics table

health_clinic_id | health_clinic_name  
--------------------------------------
1                  South Valley  
2                  North Valley  
3                  East Valley  
4                  West Valley  

The following query does what I expect, i.e because of the RIGHT JOIN all health clinics are shown even if the number of clients is 0 for a health clinic.

SELECT  
    options_health_clinics.health_clinic_name,  
    COALESCE(COUNT(clients.health_clinic_id), 0) AS n_clients

FROM clients
    RIGHT JOIN options_health_clinics ON options_health_clinics.health_clinic_id = clients.health_clinic_id

GROUP BY options_health_clinics.health_clinic_id

As soon as I throw in the WHERE clause (below) to filter out clients who have not accepted, only the health clinics with non-zero counts are shown.

WHERE clients.accepted = 1 

Why is that and how can I fix it?

Thanks, Mitchell

Answer 1

If you need non-null client acceptance then you can try this-

SELECT  
    options_health_clinics.health_clinic_name,  
    COALESCE(COUNT(clients.health_clinic_id), 0) AS n_clients

FROM clients
    RIGHT JOIN options_health_clinics ON options_health_clinics.health_clinic_id = clients.health_clinic_id

WHERE clients.accepted IS NOT NULL

GROUP BY options_health_clinics.health_clinic_id

Answer 2

When there are no clients, then clients.accepted is NULL. Try:

WHERE clients.accepted = 1 OR clients.accepted IS NULL

Answer 3

It breaks because you are right joining clients to options_health_clinics which returns all the options_health_clinics rows but not all the clients. By adding the where clause clients.accepted = 1 you filter out all the clinics without clients because clients.accepted is null for all those rows and <> 1

Answer 4

I think you want to move the WHERE condition to the ON condition.

RIGHT JOIN options_health_clinics
  ON options_health_clinics.health_clinic_id = clients.health_clinic_id
    AND clients.accepted = 1

This will include only clients where accepted is 1 before the clients are joined to the options_health_clinics.