query to get records in one table but not in another one.

[cb49747]

Hello,

I have this query that works but now I want to refine it so that it only selects records in the loaninfo table that do not have a record in the achtransfer table. I not sure how to do that. Here is the query I have now.

SELECT loaninfo.loannumber, loaninfo.investornumber, loaninfo.nextduedate, loaninfo.lastpaymentdate, achtransfer.paymentdate, achtransfer.total_amount 
          FROM loaninfo LEFT JOIN achtransfer ON loaninfo.loannumber=achtransfer.loannumber
          WHERE loaninfo.totaldelinquent > '0' AND loaninfo.upb > '0'

Any help greatly appreciated.

 

[r937]

SELECT loaninfo.loannumber
     , loaninfo.investornumber
     , loaninfo.nextduedate
     , loaninfo.lastpaymentdate
  FROM loaninfo 
LEFT 
  JOIN achtransfer 
    ON achtransfer.loannumber 
     = loaninfo.loannumber
 WHERE [b]achtransfer.loannumber is null[/b]
   and loaninfo.totaldelinquent > 0 
   AND loaninfo.upb > 0

i removed the achtransfer columns from the SELECT (since they're going to be NULL anyway), and i removed the quotes from the numeric constants

r937.com | rudy.ca

 

[cb49747]

worked like a charm,

Thanks a bunch