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)
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
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)
Code:
$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"];
}Kevin