corelated subquery to join query

  • hi

    please help wid this query

    update [copy_orders]

    set sumorders=(select sum(quantity) from [order details]  od

    where od.orderid=[copy_orders].orderid

    group by od.orderid )

    above is a corelated subquery that updates the copy_orders table with sum of quantity in order details table where the orderId matches, can it be converted to a simple join qery ?

     

    thanks

  • Without having tested anything, I guess that the following query gives the same result:

    update [copy_orders]

    set sumorders=(select sum(quantity) from [order details]  od

    where od.orderid=[copy_orders].orderid)

    That's a simplification but still not a join

     

  • I think that your requirement for row aggregation rules out a simple join query directly.  However, why not create a view of

    select orderID, sum(quantity) from [order details] group by orderID

    and then you can build your simple join using this view.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Or you could try this:

    update c

    set c.sumorders = t.sum

    from copy_orders c inner join

    (select orderid, sum(quantity) as sum from [order details] group by orderid) t

    on c.orderid = t.orderid

  • i like the idea , but isnt it same as the corelated subqueries ??

    we cannot use the views as we are not dealing directly with the data and jus makin a generic function for it.

    Amrita

  • This is my understanding of a join query

    Could you be more specific as to how you have access to data?

  • This is not strictly a corelated subquery - the subquery runs independently of the outer query, as the subquery's results do not depend on values from the outer query.  I can't see how you're going to get any better.

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thanks, Phil, for this explanation - I didn't know what a corelated subquery was. I should also mention that my query is nothing but your view idea - except that I use a virtual table (I hope that's the correct word ) instead of a view.

  • Hi Jesper

    I was actually answering the previous message; not yours, which made perfect sense.  Your solution effectively replaces my view idea with a standard (non-corelated ?) subquery and is the best solution I can think of in this case.

    Regards, Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • thanks for the query , this one seems to work nicely

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply