title: How to find records from table A, which are not present in table B?
body: <p>I use a SQL Server 2008 database.</p>
  
  <p>I have two tables with columns like these:</p>
  
  <p><code>Table A</code>:</p>
  
  <pre><code>request_id|order_id|e-mail
  100       |2567    |jack@abc.com
  100       |4784    |jack@abc.com
  450       |2578    |lisa@abc.com
  450       |8432    |lisa@abc.com
  600       |9032    |john@abc.com
  600       |9033    |john@abc.com
  </code></pre>
  
  <p><code>Table B</code> has also <code>id</code> and <code>order_no</code> columns and many others columns:</p>
  
  <p><code>Table B</code>:</p>
  
  <pre><code>request_id|order_id|e-mail
  100       |2563    |oscar@abc.com
  300       |4784    |peter@abc.com
  600       |9032    |john@abc.com
  650       |2578    |bob@abc.com
  850       |8432    |alice@abc.com
  </code></pre>
  
  <p>As you can see, a given <code>request_id</code> in table A can occur more than once (see 100 &amp; 450 records)</p>
  
  <p>I need to find all records from table A, which are not present in table B by <code>order_id</code>, but have equal <code>request_id</code> column values.</p>
  
  <p>For above example I expect something like this:</p>
  
  <p><code>Output</code>:</p>
  
  <pre><code>request_id|order_id|e-mail
  100       |2567    |jack@abc.com
  100       |4784    |jack@abc.com
  450       |2578    |lisa@abc.com
  450       |8432    |lisa@abc.com
  600       |9033    |john@abc.com
  </code></pre>
  
  <p>As you can see above records from table A are not present in table B. This criteria is only satisfied with record where <code>order_id=600</code></p>
  
  <p>I created the sketch of T-SQL query:</p>
  
  <pre><code>select 
      D.request_id, D.order_id 
  from 
      table A AS D 
  where 
      D.request_id = 600 
      and D.order_id not in (select M.order_id 
                             from table B AS M 
                             where M.request_id = 600)
  </code></pre>
  
  <p>Unfortunately I don't have idea how to transform my query for all <code>request_id</code>. The first think is to use while loop over all <code>request_id</code> from table A, but it seems not smart in SQL world.</p>
  
  <p>Thank you for any help</p>