title: Need all records of left table but IF there's a match on right table I have further restrictions
body: <p>I've found very similar questions all involving the problem of restrictions in the WHERE clause defeating the desired effect of the LEFT JOIN. But I'm not able to extend those problems and answers to my situation.</p>
  
  <p>I need all the records of left table regardless of match in the right. IF there's a match on the right table, there will likely be multiple matches, and I run a subquery to get the correct one.</p>
  
  <p>Left table has a key and a billing period name for each row, right table has the same key and dated transactions. If there's a match on the key, then I need to return just the record from the right table having a transaction date that is most recent one for the corresponding billing period in the matching left table record.</p>
  
  <p>So, because the WHERE statement for choosing the correct matching record from the right table is a subquery, I can't stick it in the ON portion of my LEFT JOIN.</p>
  
  <p>My next approach will be to go ahead and let the thing run as an inner query, then UNION those results with a second query to pick up the non-matching left-table records. That sounds awful, but I'm no stranger to brute force approaches.</p>
  
  <pre><code>key1 billing_period                   key2      date     value
   1    period_1                         1        date_1     a1
   2    period_1                         1        date_2     a2
   3    period_1                         1        date_3     a3
   4    period_1                         3        date_1     a4
                                         3        date_4     a6
  </code></pre>
  
  <p>desired output is</p>
  
  <pre><code> 1   period_1  date_2  a2
   2   period_1
   3   period_1  date_4  a6
   4   period_1
  </code></pre>
  
  <p>because a) I want all records from the left table, and b) date_2 was the correct date to choose for among those having a key value of 1, and date_4 met the criteria among all dates available having a match on a key value of 3. Those dates were the latest dates still falling within the respective period_x.</p>
  
  <p>The pseudocode is 'if there's a match key1 = key2, then for all such matches return the one having the latest date that's within the billing_period. if there is NOT a match at all, just return the left table record'.</p>