title: Efficiently Flattening a One To Many Relationship for Single Value Lookup
body: <p>I have a many to one relationship like the following:</p>
  <p><strong>Activity Table</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">ActivityId</th>
  <th style="text-align: left;">ActivityType</th>
  <th style="text-align: left;">Timestamp</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;"><em><strong>100</strong></em></td>
  <td style="text-align: left;">Account</td>
  <td style="text-align: left;">07/09/2021</td>
  </tr>
  <tr>
  <td style="text-align: left;">200</td>
  <td style="text-align: left;">Account</td>
  <td style="text-align: left;">07/09/2021</td>
  </tr>
  <tr>
  <td style="text-align: left;">300</td>
  <td style="text-align: left;">Book</td>
  <td style="text-align: left;">07/09/2021</td>
  </tr>
  <tr>
  <td style="text-align: left;">400</td>
  <td style="text-align: left;">Book</td>
  <td style="text-align: left;">07/09/2021</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Activity Detail Table</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">ActivityId</th>
  <th style="text-align: left;">ColumnId</th>
  <th style="text-align: left;">NEWVALUE</th>
  <th style="text-align: left;">OLDVALUE</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;"><em><strong>100</strong></em></td>
  <td style="text-align: left;">AcctNbr</td>
  <td style="text-align: left;">&quot;123456&quot;</td>
  <td style="text-align: left;">&quot;0123&quot;</td>
  </tr>
  <tr>
  <td style="text-align: left;"><em><strong>100</strong></em></td>
  <td style="text-align: left;">AcctName</td>
  <td style="text-align: left;">&quot;NewName&quot;</td>
  <td style="text-align: left;">&quot;Old Name&quot;</td>
  </tr>
  <tr>
  <td style="text-align: left;"><em><strong>100</strong></em></td>
  <td style="text-align: left;">AcctRole</td>
  <td style="text-align: left;">&quot;Admin&quot;</td>
  <td style="text-align: left;">[NULL]</td>
  </tr>
  <tr>
  <td style="text-align: left;">400</td>
  <td style="text-align: left;">BookAdd</td>
  <td style="text-align: left;">123</td>
  <td style="text-align: left;">[NULL]</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to be able to answer the following question:</p>
  <blockquote>
  <p>How many AccountNumber Updates where there on 07/09/2021 and what are
  the (new) AccountNumbers that have been updated?</p>
  </blockquote>
  <p>The solution I am imagining looks something like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">ActivityId</th>
  <th style="text-align: left;">ActivityType</th>
  <th style="text-align: left;">Timestamp</th>
  <th style="text-align: left;">NewAcctNbr</th>
  <th style="text-align: left;">NewAcctName</th>
  <th style="text-align: left;">NewRole</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;"><em><strong>100</strong></em></td>
  <td style="text-align: left;">Account</td>
  <td style="text-align: left;">07/09/2021</td>
  <td style="text-align: left;">&quot;123456&quot;</td>
  <td style="text-align: left;">&quot;NewName&quot;</td>
  <td style="text-align: left;">&quot;Admin&quot;</td>
  </tr>
  <tr>
  <td style="text-align: left;">400</td>
  <td style="text-align: left;">Book</td>
  <td style="text-align: left;">07/09/2021</td>
  <td style="text-align: left;">[NULL]</td>
  <td style="text-align: left;">[NULL]</td>
  <td style="text-align: left;">[NULL]</td>
  </tr>
  </tbody>
  </table>
  </div><hr />
  <p>Part of the reason I have been struggling to find resources to solve this problem is that I do not know what this type of relationship is called.</p>
  <p>I would appreciate a concrete example of the SQL I would use in this situation, but it would also be fantastic if somebody could improve my vocabulary so I can more effectively research on my own.</p>
  <p>Thank you</p>