title: Multiple JOIN view on the same table
body: <p>I have this db storing sensors acquisition data,<br>
  Acquisitions (acq) come from different Control Units (cu) at fixed intervals (datetime)<br>
  Each Acquisition has many different measures stored in Data table</p>
  
  <pre><code>acq
      id  
      datetime 
      id_cu     
  
  data          
      id        
      id_acq  
      id_meas
      value
  </code></pre>
  
  <p>I need this kind of view:</p>
  
  <pre><code>+---------------------+------+----+-----+
  |      datetime       |  v1  | v2 |  v3 |
  +---------------------+------+----+-----+
  | 2010-09-13 00:05:00 | 40.9 |  1 | 0.3 |
  | 2010-09-13 00:10:00 | 41.0 |  2 | 0.3 |
  | 2010-09-13 00:15:00 | 41.1 |  4 | 0.3 |
  +---------------------+------+----+-----+
  </code></pre>
  
  <p>AS:  </p>
  
  <ul>
  <li><p>v1 is data.value (for example a humidity)<br>
  WHERE acq.id_cu=1 AND data.id_meas=100</p></li>
  <li><p>v2 is data.value (for example a counter)<br>
  WHERE acq.id_cu=2 AND data.id_meas=200</p></li>
  <li><p>v3 is data.value (for example a temperature)<br>
  WHERE acq.id_cu=3 AND data.id_meas=300</p></li>
  </ul>
  
  <p>and so on up to dozens of combinations choosen by user</p>
  
  <p>I ended up with this query but it takes forever on a very small amount of data compared to the one that will be in production</p>
  
  <pre><code>SELECT a1.datetime, d1.value, d2.value, d3.value
  FROM
      acq a1, data d1
      JOIN acq a2, data d2
          ON a2.id=d2.id_acq AND a2.datetime=a1.datetime
      JOIN acq a3, data d3
          ON a3.id=d3.id_acq AND a3.datetime=a1.datetime
  WHERE a1.id=d1.id_acq
      AND a1.id_cu=1 AND d1.id_meas=100
      AND a2.id_cu=2 AND d2.id_meas=200
      AND a3.id_cu=3 AND d3.id_meas=300
  </code></pre>
  
  <p>I guess it would be way faster to get data separately for each <code>a1.id_centr=x AND d1.id_meas=y</code> condition and then printing data tabled that way I want with my application.</p>
  
  <p>What is the best (and correct) way to acheive this?</p>
  
  <p>edit: assuming no lacks in acquisitions I mean running this:</p>
  
  <pre><code>SELECT datetime, value
  FROM acq, data
  WHERE acq.id=data.id_acq
      AND (
          id_cu=1 AND id_meas=100
          OR id_cu=2 AND id_meas=200
          OR id_cu=3 AND id_meas=300
      )
  ORDER BY id_cu, id_meas
  </code></pre>
  
  <p>the splitting results by id_cu / id_meas change and showing results side by side using a programming language (like python + numpy) is matter of hundreths of seconds vs. ... minutes?</p>