Post reply

corelated subquery to join query

  • July 22, 2005 at 1:12 am

    #66781

    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

  • July 22, 2005 at 2:51 am

    #576328

    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

     

  • July 22, 2005 at 2:55 am

    #576330

    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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • July 22, 2005 at 3:09 am

    #576335

    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

  • July 22, 2005 at 3:21 am

    #576339

    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

  • July 22, 2005 at 3:25 am

    #576340

    This is my understanding of a join query

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

  • July 22, 2005 at 3:27 am

    #576341

    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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • July 22, 2005 at 4:32 am

    #576352

    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.

  • July 22, 2005 at 4:43 am

    #576353

    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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • July 29, 2005 at 12:40 am

    #578093

    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