Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jun 2006
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combining two queries

    Hi all,

    I think this might be a noob question, but I cant figure it out anyway,

    I'm doing an orders history page, I have some simple sql to pull out all the orders, like
    "SELECT * FROM orders WHERE orders_users_id = " & users_id

    and then as I go thru each record in the result set, I do another query,
    "SELECT SUM(payments_paymentAmount) AS sumPayments FROM payments GROUP BY payments_orders_id HAVING payments_orders_id = " & orsOrder("orders_id")

    the orsOrder("orders_id") comes from the "SELECT * FROM orders" sql query.

    Basically my question is how can I mash these together? closest I could get was
    SELECT orders.*, (SELECT SUM(payments_paymentAmount) AS sumPayments FROM payments GROUP BY payments_orders_id HAVING payments_orders_id = [orders].[orders_id]) FROM orders WHERE orders_users_id = 5806

    but no luck

    Anyway, any help is appreciated, I want the sumPayments present in that query so I can add on an order by that uses sumPayments.

    later
    James

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you need to join the order and the payments table.
    maybe post the design of these tables + the columns from each table that should be included in the result.

    + what db are you using?
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

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