title: Left Outer Join To Get All Rows?
body: <p>I'm working with an access database and I have some tables set up like this.</p>
  
  <pre><code>Table1(Table1Id, Field1, Field2, ...)
  Table2(Table2Id, Table1Id, AuditDate, Field1, Field2, ...)
  </code></pre>
  
  <p>I am trying to select every record in Table1, and those in Table2 with the latest AuditDate. If there is no entry in Table2 to tie back into Table1, I just want to populate a row of what is in Table1 and a bunch of empty fields where the data from Table2 would come from. Here is the query I have so far.</p>
  
  <pre><code>SELECT Table1.TableId, 
         Table1.Field1, 
         Table1.Field2, 
         Table2.Field1, 
         Table2.Field2 
  FROM Table1 
  LEFT OUTER JOIN Table2 ON Table1.TableId = Table2.Table1Id 
  WHERE Table2.AuditDate = (SELECT MAX(AuditDate) FROM Table2 WHERE Table1.TableId = Table2.Table1Id)
  </code></pre>
  
  <p>The problem with this is that it only returns two records. There are only two entries in Table2 but I want it to return a row of data for every record in Table1 as well instead of just the ones that find a match in Table2.</p>