paymentReceived, amount due (per order)... ,
SELECT orders.orderId, order.orderDate, orders.customerID,
customers.customerName, customerPayment.PaymentReceived
(SELECT sum(unitPrice * quantity)
FROM orderDetails
WHERE orderDetails.orderID = orders.orderID ) AS amountDue,
FROM (orders LEFT JOIN customerPayment
ON customerPayment.orderID = orders.orderID)
LEFT JOIN customer
ON customer.customerID = orders.customerID
Nomad wrote:
> > > The Database tables and fields are:
> > > Orders (orderid,customerid,Orderdate)
> > > OrderDetails(Orderid,UnitPrice,productid,quantity)
> > > Customer(Customerid, customername, address)
> > > CustomerPayments(orderid,PaymentReceived)
> > >
> > > My purpose is to track the account status of the customer for each
> order
> > > they have placed.
> > >
> > > The desired format of result obtained from the query is like this:
> > >
> > > Customername, Orderid, Orderdate,AmountReceived, AmountDue(this is a
> > > calculated column).
--
Umer Farooq
Octadyne Systems
[EMAIL PROTECTED]
+1 (519) 772-5424 voice
+1 (519) 635-2795 mobile
+1 (208) 275-3824 fax
LOOKING FOR A USED CAR IN IOWA VISIT: http://www.IowaMotors.com
WARNING: ------------------------------- The information contained in
this document and attachments is confidential and intended only for the
person(s) named above. If you are not the intended recipient you are
hereby notified that any disclosure, copying, distribution, or any other
use of the information is strictly prohibited. If you have received
this document by mistake, please notify the sender immediately and
destroy this document and attachments without making any copy of any kind.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
