title: MYSQL:SQL query to return a row only if all the rows satisfy a condition
body: <p>Consider three tables -</p>
  
  <pre><code>  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
  </code></pre>
  
  <p>Each user has a type, and types defines the trainings that each user of a particular type have to complete.</p>
  
  <p><code>training_status</code> 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.</p>
  
  <p>I would like to find out all users that have successfully completed all the trainings that they have to take.</p>
  
  <p>Here's the direction that I am thinking in:</p>
  
  <pre><code>select
    id
  from users
    join types
      using (type)
    left join training_status
      using (id,type)
  where status NOT IN(None, F);
  </code></pre>
  
  <p>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.</p>