title: How to get records from Table A that don't have equivalents in a certain status in Table B?
body: <p><strong>UPDATE</strong> based on the answer by @BrianFisher below, I tried this:</p>
  
  <pre><code>SELECT np.id AS people_id, np.first_name, np.last_name, ur.rid
  FROM new_people np
  LEFT JOIN institute.users_roles ur ON np.institute_uid = ur.uid
  AND ur.rid =8
  LEFT JOIN roster r ON np.id = r.people_id
  WHERE np.company_id =1
  AND np.active =1
  AND (r.roster_id IS NULL OR NOT (r.status =  'pending' OR r.status =  'submitted'))
  ORDER BY np.last_name, np.first_name
  </code></pre>
  
  <p>It <em>seems</em> to work; it does return what I want returned. Can anyone tell me if there's a reason it shouldn't work, or might not work with a larger data set? (i.e. is it just coincidence that it worked this time? Is it bad practice to nest statements inside an AND like that?)</p>
  
  <p>===================================</p>
  
  <pre><code>SELECT np.id AS people_id, np.first_name, np.last_name, ur.rid
  FROM new_people np
  LEFT JOIN institute.users_roles ur ON np.institute_uid = ur.uid
  AND ur.rid =8
  
  LEFT JOIN roster r ON np.id = r.people_id
  WHERE np.company_id =1
  AND np.active =1
  AND NOT (
  r.status =  'pending'
  OR r.status =  'submitted'
  )
  ORDER BY np.last_name, np.first_name
  </code></pre>
  
  <p><strong>EDIT</strong> - I've also tried the query like this with the same result:</p>
  
  <pre><code>SELECT np.id AS people_id, np.first_name, np.last_name, ur.rid
  FROM new_people np
  LEFT JOIN institute.users_roles ur ON np.institute_uid = ur.uid
  AND ur.rid =8
  
  LEFT JOIN roster r ON np.id = r.people_id AND NOT (r.status =  'pending'
  OR r.status =  'submitted')
  
  WHERE np.company_id =1
  AND np.active =1
  
  ORDER BY np.last_name, np.first_name
  
  new_people:
  id      first_name      last_name   institute_uid   company_id
  ==      ==========      =========   =============   ==========
  1       Tester          Jones       100             1
  2       Tester          Smith       200             1
  3       Tester          Brown       300             1
  4       Tester          White       400             1
  
  users_roles:
  uid    rid
  ===    ===
  100    8
  200    8
  300    8
  400    8
  
  roster:
  roster_id   people_id   company_id   status
  =========   =========   ==========   ======
  8           1           1            completed
  15          2           1            submitted
  23          3           1            pending
  </code></pre>
  
  <p>What I want this query to return is records for Tester Jones (people_id 1) because his roster record is not either pending or submitted, and Tester White, because he doesn't have a roster record at all. The query as it is returns Tester Jones, but not Tester White. How do I get it to include someone from the new_people table specifically because they DON'T have a record in the roster table? I've tried JOIN, LEFT JOIN, RIGHT JOIN, INNER JOIN on the roster table; they all return the same thing.</p>