Retrieve list of ids by comparing total -vs- specified status

Question

I have a list of clients. Each client can have several activities (0..*). Each activity contains a status `is_completed` which is a Boolean (True/False).

I need to retrieve the list of clients that have all activities completed:

• if a client has all its activities completed, I keep him.
• if a client has not all its activities completes, I ignore him.

I wrote an SQL query that does the job but I am not convinced that it is optimized:

SELECT DISTINCT cc.client_id
FROM clients_clientactivity AS cc
LEFT JOIN clients_client AS c ON (c.id = cc.client_id)
WHERE c.client_type_id = 2 
AND (
    SELECT COUNT(cc1.id) FROM clients_clientactivity AS cc1 WHERE cc1.client_id = cc.client_id
) = (
    SELECT COUNT(cc2.id) FROM clients_clientactivity AS cc2 WHERE cc2.is_completed = True AND cc2.client_id = cc.client_id
);

How can I improve it ?

Thank you for your help.

Answer 1

You could use a not in select for the not true

SELECT DISTINCT cc.client_id
FROM clients_clientactivity AS cc
LEFT JOIN clients_client AS c ON (c.id = cc.client_id)
WHERE c.client_type_id = 2 
AND cc.client_id NOT IN ( 
  SELECT cc2.client_id   
  FROM clients_clientactivity AS cc2 
  WHERE cc2.is_completed != True 
)