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]
