title: Filtering sub-query results from main query T-SQL
body: <p>I have tried </p>
  
  <pre><code>select title
  from BOOKS B, AUTHOR A, LOANS U
  where a.author_id = b.author_id
  and not B.book_id = U.book_id
  order by A.name
  </code></pre>
  
  <p>and </p>
  
  <pre><code>select title
  from BOOKS B, AUTHOR A, LOANS U
  where b.author_id = a.author_id
  and not b.book_id in (select u.book_id from LOANS)
  order by A.name, b.title
  </code></pre>
  
  <p>All I need would be results from my sub-query <code>select u.book_id from LOANS</code></p>
  
  <p>Basically the table LOANS contains a list of book_id 's which have been loaned from a library.</p>
  
  <p>I need the book_id 's which haven't been loaned yet. Then need to have them sorted alphabetically by the author's name (thus the <code>order by A.name</code>)</p>
  
  <p>Note: the table containing the books only contains the author_id. To get the author's name I need to compare that author_id to the id's in the table AUTHOR.</p>
  
  <p>Anyone that can tell me what I'm doing wrong or - if I'm using a wrong method - what method to use instead?</p>