Here you.. go.. returns.. orderId, date, customerId, customer name,
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:
> Hi Josh,
> Thanks for your reply.
> Your query is similar to what I have been trying.
>
> My query is:
>
> SELECT
> customer.Customercompanyname,
> orders.Orderid,
> orders.Orderdate,
> (Orderdetails.unitprice * Orderdetails.quantity) As OrderValue,
> CustomerPayments.PRIG As PaymentsReceived
>
> FROM Orders , OrderDetails, Customer, CustomerPayments
> WHERE 0=0
> AND Orderdetails.orderid=Orders.orderid
> AND CustomerPayments.orderid=Orders.orderid
> AND Orders.customerid=Customer.customerid
>
> The result is something like this which is wrong:
> Notice the repetition of the OrderId field. Ideally there should be one row
> for each order.
>
> Customer Name OrderID, OrderDate, OrderValue Payments
> Received.
>
> Company 1 1 11/10/2004 55
> 500
> Company 1 1 11/10/2004 31898
> 500
> Company 1 1 11/10/2004 5678
> 500
> Company5 4 11/10/2004 2320
> 400
> Company5 4 11/10/2004 4740
> 400
> Company5 4 11/10/2004 26400
> 400
> Company5 4 11/10/2004 544600
> 400
> ----- Original Message -----
> From: "Josh" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Monday, October 11, 2004 6:07 PM
> Subject: Re: SQL Query Help Please.
>
> > Here's a start, though you'll have to explain your tables and your
> > calculations further.
> >
> > SELECT
> > c.Customername,
> > a.Orderid,
> > a.Orderdate,
> > ( where is this being stored? AmountReceived.... maybe this could be
> > d.PaymentReceived AS AmountReceived ? ),
> > ( put your formula for calculating AmountDue here, possibly a subquery )
> > FROM Orders a, OrderDetails b, Customer c, CustomerPayments d
> > WHERE a.orderid = b.orderid
> > AND a.orderid = d.orderid
> > AND c.customerid = a.customerid
> >
> > Hope that helps some...
> >
> > I would recomend the SQL book by forta, or just look online for a good
> > tutorial.
> >
> > -Josh
> >
> > --
> > Exciteworks, Inc
> > Expert Hosting for less!
> > *Ask for a free 30 day trial!*
> > http://exciteworks.com
> >
> > Plans starting at -$12.95- including MS SQL Server!
> >
> >
> >
> >
> > Nomad wrote:
> >
> > > Hello!
> > >
> > > I am trying to create a join of four tables to get data from a db
> in the
> > > format I want.
> > >
> > > 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).
> > >
> > > I am using Access 2000 with CFusion.
> > >
> > > 1.)Can someone help me with the SQL syntax please. I have tried
> several
> > > permutations and combinations of sql (to the best of my knowledge) but
> > > without success.
> > > 2.) Can someone please suggest a good book on SQL syntax that will
> clear
> > > thing up for me.
> > >
> > > Many Thanks in Advance,
> > >
> > > Mark Taylor
> > > Admin
> > >
> >
> >
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
