title: Filtering in join query
body: <p>Using crystal reports version 14, MS sql server 2008</p>
  
  <p>I am joining two tables and I need to filter in the join, so if a certain value exists in one of the table, I want to join to that record, if it does not exist, I want to have a null-record. I.e:</p>
  
  <pre><code>select * from sample left outer join test
  on(sample.sample_number=test.sample_number and test.name='PREP')
  </code></pre>
  
  <p>I can run that in Sql server studio and get exactly what I want</p>
  
  <p>What I can get in crystal reports is</p>
  
  <pre><code>select * from sample left outer join test   
  on(sample.sample_number=test.sample_number) 
  where test.name='PREP'
  </code></pre>
  
  <p>In the latter case, rows where test.name='PREP' does not exist will be removed and if there are samples that have no test.name='PREP', those samples will be removed. </p>
  
  <p>Are there any ways I can do this in CR 14?</p>
  
  <p>dummy tables:</p>
  
  <p>Sample<br>
  sample_number,name<br>
  1,A<br>
  2,B<br>
  3,C  </p>
  
  <p>Test<br>
  sample_number,name<br>
  1,PREP<br>
  1,SOMETHING<br>
  2,SOMETHING<br>
  3,SOMETHING_ELSE<br>
  3,PREP  </p>
  
  <p>What I want:</p>
  
  <p>1,A,1,PREP<br>
  2,B,NULL,NULL<br>
  3,C,3,PREP  </p>
  
  <p>(of course there are more fields in the tables and a selection of which fields, but this should illustrate what I want)</p>
  
  <p>I know I can make views and query them directly in crystal, but if possible, I would avoid doing that.</p>