title: LEFT JOIN with conditions
body: <p>I am using MS SQL. I have two tables:</p>
  
  <p>1) "OrderProducts" - This table stores all the products an order has. There is another Orders main table, which we can leave out of this picture. Orders main table stores an order's main data, while the OrderProducts table stores the details.</p>
  
  <p>2) "TransactionFeeProducts" - This table stores all the products' transaction fees that I want to charge for the orders. It has a BatchID, where multiple products can have the same BatchID.</p>
  
  <p>Basically, all ordered products which OrderDate falls between TransactionFeeProducts.FromDate and TransactionFeeProducts.ToDate, will be charged TransactionFeeProducts.TransactionFee</p>
  
  <h2>OrderProducts</h2>
  
  <ul>
  <li>ProductID</li>
  <li>Quantity</li>
  <li>OrderDate</li>
  </ul>
  
  <h2>TransactionFeeProducts</h2>
  
  <ul>
  <li>BatchID</li>
  <li>ProductID</li>
  <li>FromDate</li>
  <li>ToDate</li>
  <li>TransactionFee</li>
  </ul>
  
  <p>SQL:</p>
  
  <pre><code>SELECT SUM(Quantity) as Orders, 
  TransactionFeeProducts.ProductID, FromDate, ToDate 
  FROM TransactionFeeProducts 
  LEFT JOIN OrderProducts ON TransactionFeeProducts.ProductID = OrderProducts.ProductID
  WHERE
  OrderDate &gt;= TransactionFeeProducts.FromDate AND 
  OrderDate &lt;= TransactionFeeProducts.ToDate AND 
  TransactionFeeProducts.BatchID = 3
  GROUP BY TransactionFeeProducts.ProductID, FromDate, ToDate 
  ORDER BY SUM(Quantity) DESC
  </code></pre>
  
  <p>I want the SQL to return ALL records in TransactionFeeProducts where the BatchID = 3.
  SUM(Quantity) should only give me sum where the OrderDate are made between TransactionFeeProducts.FromDate and TransactionFeeProducts.ToDate</p>
  
  <p>If SUM(Quantity) is 0, then the field should be NULL or 0.</p>
  
  <p>My problem now is, the SQL doesn't return any record if the SUM(Quantity) is 0.</p>
  
  <p>Please help. Thank you very much.</p>