PDA

View Full Version : combining two queries



digijin
Jul 14th, 2006, 06:06 AM
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 :confused:

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

raf
Jul 26th, 2006, 11:45 AM
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?