Multiple JOIN view on the same table

I have this db storing sensors acquisition data,

Acquisitions (acq) come from different Control Units (cu) at fixed intervals (datetime)

Each Acquisition has many different measures stored in Data table

acq
    id  
    datetime 
    id_cu     

data          
    id        
    id_acq  
    id_meas
    value

I need this kind of view:

+---------------------+------+----+-----+
|      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 |
+---------------------+------+----+-----+

AS:

• v1 is data.value (for example a humidity)

  WHERE acq.id_cu=1 AND data.id_meas=100

• v2 is data.value (for example a counter)

  WHERE acq.id_cu=2 AND data.id_meas=200

• v3 is data.value (for example a temperature)

  WHERE acq.id_cu=3 AND data.id_meas=300

and so on up to dozens of combinations choosen by user

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

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

I guess it would be way faster to get data separately for each a1.id_centr=x AND d1.id_meas=y condition and then printing data tabled that way I want with my application.

What is the best (and correct) way to acheive this?

edit: assuming no lacks in acquisitions I mean running this:

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 i开发者_运维问答d_cu, id_meas

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?

---

*Assuming DATETIME and data.id_acq and cu and id_meas all have indexes*, you could try a UNION query with dummy column placeholders and a kludgey MAX(). This ought to work if your data.values are not negative numbers (and if they are you could simply choose an extremely large negative number instead of zero as the dummy placeholder value, a number well outside the possible range):

    select FOO.datetime, max(FOO.v1), max(FOO.v2), max(FOO.v3)
    from
     (

     select acq.datetime, data.value as v1,0 as v2, 0 as v3
     from acq inner join data on acq.id = data.id_acq
     where acq.id_cu=1 and data.id_meas=100

     UNION

    select acq.datetime, 0 as v1, data.value as v2, 0 as v3
    from acq inner join data on acq.id = data.id_acq
    where acq.id_cu=2 and data.id_meas=200

    UNION

    select acq.datetime, 0 as v1, 0 v2, data.value as v3
    from acq inner join data on acq.id = data.id_acq
    where acq.id_cu=3 and data.id_meas=300
    ) as FOO
    group by FOO.datetime

---

your JOINS are a bit confusing (as you are mixing explicit with implicit); try this:

SELECT a1.datetime, d1.value, d2.value, d3.value
FROM
    acq a1 
       INNER JOIN data d1 ON a1.id=d1.id_acq
       INNER JOIN acq a2  ON a2.datetime=a1.datetime
       INNER JOIN data d2 ON a2.id=d2.id_acq
       INNER JOIN acq a3  ON a3.datetime=a1.datetime
       INNER JOIN data d3 ON a3.id=d3.id_acq
WHERE 1=1
    AND a1.id_centr=1 AND d1.id_meas=100
    AND a2.id_centr=2 AND d2.id_meas=200
    AND a3.id_centr=3 AND d3.id_meas=300