title: Using JOIN with DISTINCT and prioritize one table
body: <p>I am trying to combine data from 2 tables.<br/>
  Those 2 tables both contain data from the same sensor (lets say a sensor that measures CO2 with 1 entry per 10 minutes).</p>
  
  <p>The first table contains validated data. Let's call it <code>station1_validated</code>. The 2nd table contains raw data. Let's call this one <code>station1_nrt</code>.</p>
  
  <p>While the raw-data table contains live data, the validated table contains only data points that are at least 1 month old. (It needs some time to validate those data and to control it manually afterwards, this happens only once every month).</p>
  
  <p><strong>What I am trying to do now</strong> is to combine the data of those 2 tables to display live data on a website. However when validated data is available it should prioritize that data point over the raw data-point.</p>
  
  <p>The relevant columns for this are:</p>
  
  <ul>
  <li>timed [bigint(20)]: Contains the datetime as a unix timestamp in milliseconds from 1.1.1970</li>
  <li>CO2 [double]: Contains the measured concentration of CO2 in ppm (parts per million)</li>
  </ul>
  
  <p>I wrote this basic SQL:</p>
  
  <pre><code>SELECT 
      *
  FROM
      (SELECT 
          timed, CO2, '2' tab
      FROM
          station1_nrt
      WHERE
          TIMED &gt;= 1386932400000
              AND TIMED &lt;= 1386939600000
              AND TIMED NOT IN (SELECT 
                  timed
              FROM
                  station1_nrt
              WHERE
                  CO2 IS NOT NULL
                      AND TIMED &gt;= 1386932400000
                      AND TIMED &lt;= 1386939600000) UNION SELECT 
          timed, CO2, '1' tab
      FROM
          station1_validated
      WHERE
          CO2 IS NOT NULL
              AND TIMED &gt;= 1386932400000
              AND TIMED &lt;= 1386939600000) a
  ORDER BY timed
  </code></pre>
  
  <p>This does not work correctly as it selects only those data points where both tables have an entry.
  However I want to do this with a <code>JOIN</code> now as it would be much faster. However I don't know how to a JOIN with a DISTINCT (or something similar) with prioritizing a table. Could someone help me out with this (or explain it?)</p>