query doubt

adorearun · #1 (**permalink**) 07-23-07, 03:34

Hi All,
I have three tables ......
table 1 in which we have
---------
id | name

table 2
---------
id | name | value | time

table 3
-------------
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.....

your help is appreciated
sorry for the long mail

Thanx n advance,
Arun

r937 · #2 (**permalink**) 07-23-07, 07:02

Code:

insert 
  into t3 
     ( id , name , avgval , c1 , c2 , c3 , c4 , c5 )
select id
     , name
     , AVG(value)
     , ( 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 )
  from t2 
group 
    by id
     , name

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

adorearun · #3 (**permalink**) 07-23-07, 08:40

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).

requirement:

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.

thanks
Arun

r937 · #4 (**permalink**) 07-23-07, 08:59

you have lost me completely

if you wish to proceed, please show your real queries

adorearun · #5 (**permalink**) 07-24-07, 00:52

Hi,
Im sorry for not posting the real query,may be it create some problems for me.But i thank you very much for your intention to help.

Thanks for your help
Arun

guelphdad · #6 (**permalink**) 07-24-07, 08:25

Please don't waste people's time by posting a query that has nothing to do with your real queries.

Internet Services

Web Development

Digital Marketing

Consumer Tech