Results 1 to 6 of 6

Thread: query doubt

  1. #1
    Join Date
    Nov 2006
    Posts
    34

    Unanswered: query doubt

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2006
    Posts
    34
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have lost me completely

    if you wish to proceed, please show your real queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2006
    Posts
    34
    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

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    Please don't waste people's time by posting a query that has nothing to do with your real queries.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •