title: R Data.Table Join on Conditionals
body: <p>I have two tables that I would like to join together in a way equivalent to the following SQL, where I join on multiple conditions, not just equality.</p>
  
  <pre><code>require(sqldf)
  require(data.table)
  
  dt &lt;- data.table(num=c(1, 2, 3, 4, 5, 6), 
  char=c('A', 'A', 'A', 'B', 'B', 'B'), 
  bool=c(TRUE, FALSE, TRUE, FALSE, TRUE, FALSE))
  
  dt_two &lt;- data.table(
  num =c(6, 1, 5, 2, 4, 3), 
  char=c('A', 'A', 'A', 'B', 'B', 'B'), 
  bool=c(TRUE, FALSE, TRUE, FALSE, TRUE, FALSE))
  
  
  dt_out_sql &lt;- sqldf('
      select dtone.num, dtone.char, dtone.bool, SUM(dttwo.num) as SUM,  
     MIN(dttwo.num) as MIN
      from dt as dtone INNER join dt_two as dttwo on 
      (dtone.char = dttwo.char) and 
      (dtone.num &gt;= dttwo.num OR dtone.bool)
  GROUP BY dtone.num, dtone.char, dtone.bool')
  </code></pre>
  
  <p>I would like to avoid the SQL solution, for both performance and flexibility reasons.  The same goes for doing a cross join, and then filtering/aggregating -- it would create an intermediate table with lots of unnecessary records for me to filter out.</p>
  
  <p>Thank you very much!</p>
  
  <p>Update -- my initial example was done in haste.  In my actual problem, I'm not doing a self join.</p>