title: LEFT OUTER JOIN in sqldf with WHERE and HAVING clauses
body: <p>I have two data frames in R and I would like to conditionally merge them on <code>id</code> and <code>day</code>. The merge is that the <code>right</code> variables merged to the <code>left</code> variables be as new/fresh/recent as possible, but must be at least three days old.</p>
  
  <p>But, if there isn't a match in <code>right</code> to my <code>id</code>-<code>date</code> pair in <code>left</code> I'd still like to retain them. My study has two parts, so I don't want to drop the <code>id</code>-<code>day</code> observations just because they're not complete.</p>
  
  <p>Can I do this in one <code>sqldf</code> step? My current approach requires an additional base R <code>merge</code>.</p>
  
  <pre><code>left &lt;- data.frame(id=rep(1:5, each=10),
                     day=rep(1:10, times=5),
                     x=rnorm(5*10))
  right &lt;- data.frame(id=rep(1:2, each=21),
                     day=rep(-10:10, times=2),
                     y=rnorm(2*21))
  combined &lt;- sqldf("SELECT L.id, L.day, L.x, R.y
                    FROM left L LEFT OUTER JOIN right R
                    ON (L.id = R.id)
                    WHERE ((L.day - R.day) &gt;= 3)
                    GROUP BY L.id, L.day
                    HAVING (R.day = MAX(R.day))")
  combined                  
  
  combined.2 &lt;- merge(left, combined, all=TRUE)
  combined.2 
  </code></pre>