title: Left Outer Join on Multiple Tables hides data with selection expert- Crystal Reports
body: <p>I'm having trouble with crystal reports in reporting on two tables in a one to many relationship.</p>
  
  <hr>
  
  <p>Example Tables:</p>
  
  <p><strong>dbo.stock</strong><br/></p>
  
  <pre><code>Barcode   Qty   
  12345     3     
  11111     5     
  22222     6     
  </code></pre>
  
  <p><strong>dbo.sales</strong></p>
  
  <pre><code>Barcode    SalesDate    QtySold&lt;br/&gt;
  12345      1/1/2013     1          
  12345      1/1/2012     1
  12345      1/1/2010     1
  11111      1/1/2000     1
  </code></pre>
  
  <p>Tables are linked via </p>
  
  <pre><code>dbo.stock Left Outer Join On dbo.sales             
  </code></pre>
  
  <hr>
  
  <p>Report configuration:</p>
  
  <pre><code>Group 1 Header |
  Sales          |Sales.barcode Sales.QtySold
  Group 1 Footer |Group(Barcode) Sum(QtySold) Stock.Qty
  </code></pre>
  
  <hr>
  
  <p>The Problem:
  Display Sales &amp; Stock (without sales date restriction)</p>
  
  <pre><code>Barcode    Sold    OnHand
  12345      3       3
  11111      1       5
  22222              6
  </code></pre>
  
  <p>But when a data range is selected then output removes valid products with sales outside the selected data range. Say 1/1/2012 - 1/1/2013</p>
  
  <pre><code>Barcode    Sold    OnHand
  12345      2       3
  22222              6
  </code></pre>
  
  <hr>
  
  <p>I have a version that works using a sub report but the overhead is far to large to use as the sub-report runs for each line.</p>
  
  <p>Can think of a way around this? (without using sub-reports)</p>
  
  <p>Thanks B</p>
  
  <hr>
  
  <p>System Notes:<br/>
  Crystal 8.5
  Database .dbf file access via ODBC driver</p>