Databases
StackOverflow2013
Postsdbo
POHow do I get a single result, from two tables, where the 2nd table contains an updated version of a record from the 1st?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I get a single result, from two tables, where the 2nd table contains an updated version of a record from the 1st?
    text
    copied!<p>I have two tables, <code>CompanyAddresses</code> &amp; <code>MyCompanyAddresses</code>. <em>(Names changed to protect the guilty).</em></p> <p><code>CompanyAddresses</code> holds a list of default addresses for companies. These records are immutable. The user can change the details of a company address, but those changes are stored <code>MyCompanyAddresses</code>.</p> <p>How can I produce a single list of addresses from both tables, excluding records from <code>CompanyAddresses</code> where a corresponding record exists in <code>MyCompanyAddresses</code>?</p> <h3>Sample Data</h3> <p>CompanyAddresses</p> <pre><code>DatabaseId | Id | Code | Name | Street | City | Zip | Maint Date 1 | Guid1 | APL | Apple | 1 Infinite Loop | Cupertino | 95014 | 11/1/2012 2 | Guid2 | MS | Microsoft | One Microsoft Way | Redmond | 98052 | 11/1/2012 </code></pre> <p>MyCompanyAddresses</p> <pre><code>DatabaseId | Id | Code | Name | Street | City | Zip | Maint Date 5 | Guid3 | APL | Apple | Updated Address | Cupertino | 95014 | 11/6/2012 </code></pre> <p>Desired Results</p> <pre><code>DatabaseId | Id | Code | Name | Street | City | Zip | Maint Date 2 | Guid2 | MS | Microsoft | One Microsoft Way | Redmond | 98052 | 11/1/2012 5 | Guid3 | APL | Apple | Updated Address | Cupertino | 95014 | 11/6/2012 </code></pre> <p>I've tried various permutations of MS SQL's <code>UNION</code>, <code>EXCEPT</code> &amp; <code>INTERSECT</code> to no avail. Also, I don't believe <code>JOIN</code>'s are the answer either, but I'll be happily proven wrong.</p> <p>The database design <em>can</em> be changed, but it would be preferable if it stayed the same.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload