
August 13th, 2012, 07:37 AM
|
|
Registered User
|
|
Join Date: Aug 2012
Posts: 5
Time spent in forums: 1 h 4 m 31 sec
Reputation Power: 0
|
|
|
Multiple Joins Formatting
Here is the basic setup. I have three tables (orders, customers, CC authorizations) and I'm trying to get a listing of all orders that were entered for customers with credit card terms but for which a credit card was not authorized. I started with a different query (that worked) but only used the customer and orders tables, but now I am getting an error. I suspect the problem lies in the format of my JOIN statements. Any help is greatly appreciated.
Code:
SELECT ct.ordno AS "Order", c.armname AS "Customer", ct.ordamt AS "Total", (ct.ordamt - ct.costamt) AS "Margin", c.termscode AS "Terms", cc.approved AS "Approved?"
FROM customer AS c
JOIN ordrhist AS ct, accauth AS cc
ON c.armacct = ct.acct_no, ct.ordno = cc.ordno
WHERE
ct.orddate = curdate() - 0 AND SUBSTRING(ct.status,1,1)<>'C'AND SUBSTRING(ct.status,2,1)='O' AND ct.hold <> 'Y' AND ct.complete <> 'I' AND c.termscode = 12
ORDER BY ordno
|