Find average temp for all samples using SQL

[beakerboy]

I have two tables:
sample has three columns; s_id (int), s_start(timestamp), and s_end(timestamp)
temp has two columns; t_timestamp (timestamp), and t_temp (real)

I want to do the following:
for each row in sample, return the s_id and the average temperature from temp between the times s_start and s_end.

I can do a foreach loop in my PHP code (PHPish psudocode follows)

$value=array()
$sample = "SELECT * FROM sample"
foreach ($sample as $key=>$value){
    $result = "SELECT avg(t_temp) AS ave FROM temp WHERE t_timestamp > $value["s_start"] AND t_timestamp < $value["s_end"]";
    $value[$key]["ave"] = $result["ave"];
}

or I can make a very long query in the same way and use a UNION to join all the cycles through the foreach loop together. However, there has to be a way to make one concise query to handle this, no?

Kevin

 

[PHV]

SELECT S.s_id, AVG(T.t_temp) AS ave
FROM sample S INNER temp T ON T.t_timestamp BETWEEN S.s_start AND S.s_end
GROUP BY S.s_id

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[beakerboy]

Thanks!!
This opens up a new window for me. I've never used anything except NATURAL INNER JOINs. I've never even considered anything besides PK, FK joins on equal values, so this will have some broad-reaching consequences in a lot of my projects.

I'll try this in my PostgreSQL database, but a stupid MS-Access test doesn't like the BETWEEN comparator. However, the following equivalent statement does work in Access:
SELECT S.s_id, AVG(T.t_temp) AS ave
FROM sample S INNER JOIN temp T ON (T.t_timestamp > S.s_start AND T.t_timestamp < S.s_end)
GROUP BY s_id