title: Sqldf join using row id in r
body: <p>For the data structure in the table below (peak_refined) I'd like to match the column ID with the unique row.names of the second table ('dat'). I'd prefer to use the sqldf package but stuck with referencing the row.name. Tested code given below for any pointers. Ideally I expect to retrieve the 'Time' from records 1 and 6 of table 'dat'.</p>
  <p><strong>Table peak_refined</strong></p>
  <pre><code>structure(list(IDs = c(1L, 6L, 28L, 31L, 668L, 2841L, 2862L, 
  2865L, 2906L, 2917L, 2925L, 2957L, 2960L, 2966L, 2984L, 2993L, 
  3000L)), class = &quot;data.frame&quot;, row.names = c(&quot;X1&quot;, &quot;X2&quot;, &quot;X3&quot;, 
  &quot;X4&quot;, &quot;X5&quot;, &quot;X6&quot;, &quot;X7&quot;, &quot;X8&quot;, &quot;X9&quot;, &quot;X10&quot;, &quot;X11&quot;, &quot;X12&quot;, &quot;X13&quot;, 
  &quot;X14&quot;, &quot;X15&quot;, &quot;X16&quot;, &quot;X17&quot;))
  </code></pre>
  <p><strong>Table Dat:</strong></p>
  <pre><code>structure(list(Time=c(&quot;2021-06-18 12:36:00&quot;, &quot;2021-06-18 12:38:00&quot;, &quot;2021-06-18 12:40:00&quot;, 
  </code></pre>
  <p>&quot;2021-06-18 12:42:00&quot;, &quot;2021-06-18 12:44:00&quot;, &quot;2021-06-18 12:46:00&quot;,
  &quot;2021-06-18 12:48:00&quot;, &quot;2021-06-18 12:50:00&quot;, &quot;2021-06-18 12:52:00&quot;,
  &quot;2021-06-18 12:54:00&quot;)))</p>
  <pre><code> [1] &quot;2021-06-18 12:36:00&quot; &quot;2021-06-18 12:38:00&quot; &quot;2021-06-18 12:40:00&quot;
   [4] &quot;2021-06-18 12:42:00&quot; &quot;2021-06-18 12:44:00&quot; &quot;2021-06-18 12:46:00&quot;
   [7] &quot;2021-06-18 12:48:00&quot; &quot;2021-06-18 12:50:00&quot; &quot;2021-06-18 12:52:00&quot;
  [10] &quot;2021-06-18 12:54:00&quot;
  </code></pre>
  <p><strong>Tested Code:</strong></p>
  <pre><code>library(sqldf)
  sq &lt;- sqldf(&quot;SELECT peak_refined.IDs, dat.row.names
  FROM peak_refined
  INNER JOIN dat
  ON peak_refined.IDs = dat.row.names&quot;)
  </code></pre>