On Fri, Oct 04, 2002 at 12:36:30PM -0700, David McInnis wrote: > Can someone please help me with the following? Normally I would do this > with a nested select, but since this is not available in MySQL I think I > need help. > > Here is what I have: An order table with sales tax total and an > orderdetail table with ordered, itemid and qty. > > What I need to do is form a sql query that will allow me to pull get the > tax amount on all orders where product id is 1, 2 or 3 for example. The > problem that I have is when I do a straight join on > > select tax from orders, orderdetail where orders.id = > orderdetail.orderid and (productid = 1 or productid = 2 or productid = > 3) > > I can get multiple tax amounts where an order has multiple matching > records in orderdetail. I know that I can group by order.id, but what I > eventually need to do is pull sum(tax) and not just tax.
I'm not certain if I understand what you're after... If you want total tax per order, try select sum(tax) as tax, orders.id from orders, orderdetail where orders.id = orderdetail.orderid and productid in (1,2,3) group by orderid; Otherwise, please clarify what you want in your desired result set. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
