title: dplyr: want to add only 1 y column during a join, not all y columns
body: <p>I need to join a table with itself in order to realize inheritance of a value in one column, as follows:
  There are two types of rows, <code>base</code> and <code>dep</code> (for "dependent").
  All rows have a <code>key</code>, but <code>dep</code> rows also have a <code>basekey</code> referring to a <code>base</code> row. There is a column <code>val</code> and any number of other columns.</p>
  
  <p><strong>My goal:</strong> Obtain all <code>dep</code> rows, with their <code>val</code> replaced by the <code>val</code> of the corresponding <code>base</code> row.</p>
  
  <p>Here is an example:</p>
  
  <pre><code>library(tidyverse)
  
  tb = tribble(
      ~key, ~type, ~basekey, ~val, ~col5, ~colN,
      "a", "base", NA,       17,   1,      100,
      "b", "base", NA,       21,   4,      130,
      "d", "dep",  "a",      NA,   11,     320,
      "g", "dep",  "a",      NA,   14,     420,
      "n", "dep",  "a",      NA,   13,     230,
      "y", "dep",  "b",      NA,   66,     665
  )
  </code></pre>
  
  <p>My actual data has a few hundred rows and <code>N=20</code> columns, so runtime or memory performance is not an issue, but operations requiring a complete column list should be avoided.</p>
  
  <h1>sqldf</h1>
  
  <p>Previously, I used the <code>sqldf</code> package, which can express my requirement nicely:</p>
  
  <pre><code>sqldf("select tb.*, tb2.val as baseval 
         from tb, tb as tb2 
         where tb2.key = tb.basekey")
  </code></pre>
  
  <p>The result is almost exactly what I want; I only need to map <code>baseval</code> to <code>val</code> or live with the longer name (which is OK for me):</p>
  
  <pre><code>  key type basekey val col5 colN baseval
  1   d  dep       a  NA   11  320      17
  2   g  dep       a  NA   14  420      17
  3   n  dep       a  NA   13  230      17
  4   y  dep       b  NA   66  665      21
  </code></pre>
  
  <h1>dplyr</h1>
  
  <p>But then I started learning <code>ggplot2</code> and encountered the <a href="https://www.tidyverse.org/" rel="nofollow noreferrer">tidyverse</a>. 
  After some reading, I decided to throw myself into its arms and rework my code according to its readable and tidy style. </p>
  
  <p><code>readr</code> and <code>tibble</code> worked like a charm, but with <code>dplyr</code> I am not yet entirely happy; my use case appears to be one that is not supported well (maybe it's just that my thinking is not taking the best route yet).</p>
  
  <p>I need to do a left join, but although all I want is the <code>val</code> column, there appears to be no method (as of <code>dplyr</code> 0.7.4) for suppressing the others:</p>
  
  <pre><code>tbj = left_join(filter(tb, type=='dep'), tb, by=c(basekey='key'))
  </code></pre>
  
  <p>which gives me this:</p>
  
  <pre><code># A tibble: 4 x 11
      key type.x basekey val.x col5.x colN.x type.y basekey.y val.y col5.y colN.y
    &lt;chr&gt;  &lt;chr&gt;   &lt;chr&gt; &lt;dbl&gt;  &lt;dbl&gt;  &lt;dbl&gt;  &lt;chr&gt;     &lt;chr&gt; &lt;dbl&gt;  &lt;dbl&gt;  &lt;dbl&gt;
  1     d    dep       a    NA     11    320   base      &lt;NA&gt;    17      1    100
  2     g    dep       a    NA     14    420   base      &lt;NA&gt;    17      1    100
  3     n    dep       a    NA     13    230   base      &lt;NA&gt;    17      1    100
  4     y    dep       b    NA     66    665   base      &lt;NA&gt;    21      4    130
  </code></pre>
  
  <p>Whew! To get what I want, I now need to</p>
  
  <ul>
  <li>Map <code>val.y</code> to <code>val</code>.</li>
  <li>Kick out all the other <code>*.y</code> columns</li>
  <li>Kick out <code>val.x</code></li>
  <li>Rename the other <code>*.x</code> columns to remove the suffix</li>
  </ul>
  
  <p>This sounds laborious.
  How to do it best?
  Is this even the right approach or is there a more <code>dplyr</code>-ish way to achieve my goal?</p>