MYSQL:SQL query to return a row only if all the rows satisfy a condition

Question

Consider three tables -

  users

    id      |   type
 -----------|------------
    1       |   a
    2       |   b   
    3       |   c

 types  

    id      |   type
 -----------|------------
    a       |   X
    a       |   Y   
    b       |   X
    c       |   X
    c       |   Y
    c       |   Z

 training_status

    id      |   training|   status
 -----------|-----------|-------------
    1       |   X       |   F
    2       |   X       |   S
    2       |   Y       |   S
    3       |   X       |   F
    3       |   Y       |   S

Each user has a type, and types defines the trainings that each user of a particular type have to complete.

training_status contains status of all the trainings that a user has taken and its result (S,F). It a user is yet to take a training, there won't be any row for that training.

I would like to find out all users that have successfully completed all the trainings that they have to take.

Here's the direction that I am thinking in:

select
  id
from users
  join types
    using (type)
  left join training_status
    using (id,type)
where status NOT IN(None, F);

Obviously this is not the right query because even if the user has completed one of the trainings, we get that row. In the aforementioned example, I'd like to get id = 2 because he has completed both trainings of its type.

Answer 1

Try

SELECT DISTINCT u.id
  FROM users u JOIN types t
    ON u.type = t.type LEFT JOIN training_status s
    ON u.id = s.id AND t.training = s.training
 WHERE s.status IS NOT NULL 
 GROUP BY u.id
HAVING COUNT(t.type) = SUM(CASE WHEN s.status = 'S' THEN 1 ELSE 0 END)

or

SELECT DISTINCT u.id
  FROM users u JOIN types t
    ON u.type = t.type LEFT JOIN training_status s
    ON u.id = s.id AND t.training = s.training
 GROUP BY u.id
HAVING MAX(s.status IS NULL OR s.status = 'F') = 0

Output:

+------+
| id   |
+------+
|    2 |
+------+

Here is SQLFiddle demo