Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006
    Location
    NY
    Posts
    14

    Unanswered: how come the result is not in this list? (it works fine when I pinpoint to it)

    Hi there,
    I've been trying to understand this problem for a while - no luck!
    assistance would highly appropriated.

    here's the deal:

    I have this query:

    SELECT D.debit_id, D.date, D.status_2, D.GOF, TA.legal_name, TA.taid, TAP.totalPay, TD.totalDebit AS amountDue
    FROM TomasAar AS TA, debit AS D

    LEFT JOIN (SELECT SUM( Amount ) AS totalPay, debit_id
    FROM ta_payments
    GROUP BY debit_id ) AS TAP ON ( TAP.debit_id = D.debit_id )

    LEFT JOIN ( SELECT SUM( debit_amount ) AS totalDebit, debit_id
    FROM transaction_debit
    WHERE transaction_status =0
    GROUP BY debit_id) AS TD ON ( TD.debit_id = D.debit_id )

    WHERE D.GOF= TA.GOF
    AND D.status_2 =5
    AND D.GOF=10001
    GROUP BY TD.debit_id
    ORDER BY TA.GOF

    this return the following result:

    94....2007-06-15.....10001....Peterson....993...null....nulll

    when I remove the line: AND D.GOF=10001
    I get a list where 10001 is not in it...WHY???

    * initially I though it's becuase of the null...nulll - but the list has some values of null...null

    thanks for any pointers

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by xianwinwin
    when I remove the line: AND D.GOF=10001
    I get a list where 10001 is not in it...WHY???
    because you took out that line, that's why

    tip: never mix comma-delimited table syntax with JOIN syntax

    here is how i would write your query:
    Code:
    SELECT D.debit_id
         , D.date
         , D.status_2
         , D.GOF
         , TA.legal_name
         , TA.taid
         , TAP.totalPay
         , TD.totalDebit AS amountDue
      FROM TomasAar AS TA
    INNER
      JOIN debit AS D
        ON D.GOF = TA.GOF
       AND D.status_2 = 5
       AND D.GOF = 10001
    LEFT OUTER
      JOIN ( SELECT SUM( Amount ) AS totalPay
                  , debit_id 
               FROM ta_payments
            GROUP BY debit_id ) AS TAP 
        ON TAP.debit_id = D.debit_id  
    LEFT OUTER
      JOIN ( SELECT SUM( debit_amount ) AS totalDebit
                  , debit_id 
               FROM transaction_debit
              WHERE transaction_status =0
             GROUP BY debit_id ) AS TD 
        ON TD.debit_id = D.debit_id  
    ORDER 
        BY TA.GOF
    notice anything else? no GROUP BY!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2006
    Location
    NY
    Posts
    14
    Wow....thank You Thank You Thank You!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •