[xianwinwin]

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

[r937]

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:

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!

[xianwinwin]

Wow....thank You Thank You Thank You!!!!