#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    5
    Rep 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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Code:
    SELECT ct.ordno AS "Order"
         , c.armname AS "Customer"
         , ct.ordamt AS "Total"
         , ct.ordamt - ct.costamt AS "Margin"
         , 12 AS "Terms"
         , cc.approved AS "Approved?"
      FROM ordrhist AS ct
    INNER  
      JOIN customer AS c
        ON c.armacct = ct.acct_no
       AND c.termscode = 12
    INNER
      JOIN accauth AS cc
        ON cc.ordno = ct.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' 
    ORDER 
        BY ct.ordno

    Comments on this post

    • maluktuk agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    5
    Rep Power
    0
    This worked perfectly. Thank you for your help.

IMN logo majestic logo threadwatch logo seochat tools logo