title: Retrieving only rows for which a join has a dependancy
body: <p>I am creating a testing system where users are allowed to re-test until they have passed.  I would like to get a list, for a given UserID, of tests which are assigned to them which they have scored less than passing (100% for this example) on.</p>
  
  <p>I have the following tables:</p>
  
  <blockquote>
    <p>(Everything here has been adapted for simplicity, but it should all be valid still)</p>
  </blockquote>
  
  <pre><code>Users
  (Generic "users" table with UserID and Name, etc...)
  
  Tests
  +--------+----------+------------------+
  | TestID | TestName | OtherTestColumns |
  +--------+----------+------------------+
  |      1 |   Test 1 |         Blah.... |
  |      2 |   Test 2 |         Blah.... |
  |      3 |   Test 3 |         Blah.... |
  |      4 |   Test 4 |         Blah.... |
  +--------+----------+------------------+
  
  Users_Have_Tests
    Users are assigned tests they must take with this table
  +--------+--------+
  | UserID | TestID |
  +--------+--------+
  |      1 |      1 |
  |      1 |      2 |
  |      1 |      3 |
  |      2 |      1 |
  |      2 |      2 |
  |      2 |      3 |
  +--------+--------+
  
  TestResults
  +--------+--------+------------+
  | TestID | UserID | Percentage |
  +--------+--------+------------+
  |      1 |      1 |         75 |
  |      1 |      1 |        100 |
  |      2 |      1 |         80 |
  |      2 |      1 |        100 |
  |      1 |      2 |        100 |
  |      2 |      2 |         75 |
  +--------+--------+------------+
  </code></pre>
  
  <p>The following query works for me to figure out ALL tests which are assigned to a user, but I want to remove tests they have passed from this list</p>
  
  <pre><code>SELECT DISTINCT
      TestID,
      TestName
  FROM
      `Users` AS u
  RIGHT JOIN
      `Users_have_Tests` AS ut
      ON
          u.UserID = ut.UserID
  LEFT JOIN
      `Tests` AS t
      ON
          ut.TestID = t.TestID
  WHERE UserID = 1
  </code></pre>
  
  <p>I feel like I need a subquery here.  I came up with the following query to find the max scores for each test a user has taken, but I'm not quite sure how to tie them together.  (Subqueries are very new to me and I'm still trying to understand them.)</p>
  
  <pre><code>SELECT DISTINCT
      TestID,
      MAX(Percentage)
  FROM
      `TestResults`
  WHERE UserID = 1
  GROUP BY TestID
  </code></pre>
  
  <p>The results I am looking for are:</p>
  
  <pre><code>UserID = 1
  +--------+----------+
  | TestID | TestName |
  +--------+----------+
  |      3 |   Test 3 |
  +--------+----------+
  
  UserID = 2
  +--------+----------+
  | TestID | TestName |
  +--------+----------+
  |      2 |   Test 2 |
  |      3 |   Test 3 |
  +--------+----------+
  </code></pre>
  
  <p>I have tried several manners of joins and conditions, but I can't quite wrap my head around what exactly I need here.  Google and StackOverflow have failed me; Likely because I'm lacking the knowledge of what to search for.</p>
  
  <p><strong>EDIT</strong>
  The following is what worked for me:</p>
  
  <pre><code>SELECT DISTINCT t.TestID, t.TestName
      FROM 'Users' AS u
      RIGHT JOIN 'Users_have_Tests' AS ut
          ON u.UserID = ut.UserID
      LEFT JOIN 'Tests' AS t
          ON ut.TestID = t.TestID
      WHERE t.TestID NOT IN (
          SELECT tr.TestID
              FROM TestResults AS tr
              WHERE tr.Percentage &gt;= 100
              AND tr.UserID = 2
          )
      AND u.UserID = 2
  </code></pre>