Help me with this query

Hi All,

I have to table, Order and OrderPayment, and the schema looks like this:

Order
----
OrderID,
InvoiceDT,
InvoiceTotal

OrderPayment
--------
PaymentID
OrderID
PaymentAmount
PaymentDT

And I want to get all the order with Invoice DT Between certain date. Here is my query:

SELECT

o.OrderID,

o.InvoiceDT, o.InvoiceTotal,

op.PaymentAmount

FROM

[Order] o

LEFTJOIN[OrderPayment] opON op.OrderID= o.OrderID

WHERE

o.InvoiceDTISNOTNULLAND

(o.InvoiceDTBETWEEN'09/01/2006'AND'09/20/2006')

But I get two results with the same orderID, eventhough I use GROUP BY

Any idea...

Try something like this

SELECT o.OrderID,o.InvoiceDT, o.InvoiceTotal,op.PaymentAmount

FROM [Order] o
LEFT JOIN (Select orderID, sum(paymentamount) PaymentAmount from [OrderPayment] group by orderid) op ON op.OrderID = o.OrderID
WHERE o.InvoiceDT IS NOT NULL AND (o.InvoiceDT BETWEEN '09/01/2006' AND '09/20/2006')
group by o.OrderID,o.InvoiceDT,o.InvoiceTotal

|||Thanks, it works...