I have three tables ......
table 1 in which we have
id | name
id | name | value | time
id | name | avg val |c1 | c2 | c3 | c4 | c5
Table one is resource table ,Table two is resource table with values for time say for one hour we have five entries and table three is archiving table where we archive the value for one hour.
Now we use
insert into t3 select value from t2 group by resid
by this query the value in t2 table is averaged to one hour and inserted into t3.
my requirement is we need to insert the all five entries in the respective column c1,c2,c3,c4,c5 for respective resid while inserting the archived entry.
one way of doing this query the resid from t1 and then query the t2 table with the result set obtained in the previous query and then insert into t3 ......
this approach will use three queries .........
is there any other approach to achieve this using single query.....
( id , name , avgval , c1 , c2 , c3 , c4 , c5 )
, ( select MIN(c1) from t2 where id = 1 )
, ( select MIN(c2) from t2 where id = 2 )
, ( select MIN(c3) from t2 where id = 3 )
, ( select MIN(c4) from t2 where id = 4 )
, ( select MIN(c5) from t2 where id = 5 )
if this is not what you want, then it is because you've done such a wonderful job hiding your real table and column names and explaining the situation
thanks for your reply and compliment.
In your query you have hardcoded the id but in my case i dono the id ,the id will be present in t1 and t2.
now what happens in my case is ....
1) we are inserting hourly archiving value (meaning for every hour) in t3 from t2 using t1 (we use group by id by comparing t1.id=t2.id).
2) I need to insert the raw value in the t3 along with the above insert .......for specific id ,but i dono the which id they are inserting for every hour in 1)...if i know the id then only i can query the t2 and get the raw value.