Find records without any corresponding record in another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following query

SELECT tblLoanData.LoanID, Max(tblPayments.Date) AS LastPaymen
FROM tblLoanData LEFT JOIN tblPayments ON tblLoanData.LoanID = tblPayments.LoanI
GROUP BY tblLoanData.LoanID, tblLoanData.Deleted, tblPayments.Deleted, tblPayments.Typ
HAVING (((tblLoanData.Deleted)=False) AND ((tblPayments.Deleted)=False) AND ((tblPayments.Type)='Full/Partial Payment')

So I want the LoanID and last payment date (loan is not deleted, payment is not deleted
This works fine unless there is no payment for a particular loan. How do I rework the query to give me a null value for last payment if there is no payment record

Thanks in advance!
 
Try

SELECT tblLoanData.LoanID, Max(tblPayments.Date) AS LastPayment
FROM tblLoanData LEFT JOIN tblPayments ON
tblLoanData.LoanID = tblPayments.LoanID
GROUP BY tblLoanData.LoanID, tblLoanData.Deleted,
tblPayments.Deleted, tblPayments.Type
HAVING (((tblLoanData.Deleted)=False) AND
((tblPayments.Deleted)=False) AND
((tblPayments.Type)='Full/Partial Payment'))
UNION
SELECT tblLoanData.LoanID , Null
FROM tblLoanData
WHERE tblLoanData.LoanID NOT IN (SELECT DISTINCT
tblPayments.LoanID FROM tblPayments)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have the following query:

SELECT tblLoanData.LoanID, Max(tblPayments.Date) AS LastPayment
FROM tblLoanData LEFT JOIN tblPayments ON
Click to expand...
tblLoanData.LoanID = tblPayments.LoanID
GROUP BY tblLoanData.LoanID, tblLoanData.Deleted,
Click to expand...
tblPayments.Deleted, tblPayments.Type
HAVING (((tblLoanData.Deleted)=False) AND
Click to expand...
((tblPayments.Deleted)=False) AND
((tblPayments.Type)='Full/Partial Payment'))
So I want the LoanID and last payment date (loan is not
Click to expand...
deleted, payment is not deleted)
This works fine unless there is no payment for a
Click to expand...
particular loan. How do I rework the query to give me a
null value for last payment if there is no payment record?
 
No, that's not quite it. I should have given more explanation..

tblPayments has all types of payments in it. I want the last payment of type "Full/Partial Payment" that has been made on a loan, unless there is NO payment of type "Full/Partial Payment." In that case, I want it to return null. It is very likely that the particular loan will have a payment record of another type ("Loan Fee"), which is why I can't just ask for the Max(Date). If the loan has had a payment of type "Full/Partial Payment", it works just fine. But, if there is no payment of that type for that loan, it just drops out of the query altogether

Am I asking for the impossible?
 
Thanks for the extra explanantion. I still believe that
the UNION query is the best solution, it just needs a
little refinement given the extra info detailed below.
Give this a try as the second part of the UNION
UNION
SELECT tblLoanData.LoanID , Null
FROM tblLoanData
WHERE tblLoanData.LoanID NOT IN (SELECT DISTINCT
tblPayments.LoanID FROM tblPayments WHERE
T2.Type='Full/Partial Payment'))

My original reply did identify those loans that had no
information at all in the Payments table. If you need to
include those in the results then you should include that
select to make a three part UNION query.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
No, that's not quite it. I should have given more explanation...

tblPayments has all types of payments in it. I want the
Click to expand...
last payment of type "Full/Partial Payment" that has been
made on a loan, unless there is NO payment of type
"Full/Partial Payment." In that case, I want it to return
null. It is very likely that the particular loan will have
a payment record of another type ("Loan Fee"), which is why
I can't just ask for the Max(Date). If the loan has had a
payment of type "Full/Partial Payment", it works just fine.
But, if there is no payment of that type for that loan, it
just drops out of the query altogether.
 
OK, that gives me the correct results. Now, to be picky, it takes about 15 seconds to run that query, which is fast enough, I suppose, but seems slow. Any way to avoid that performance hit?
 
First thing to do is to check the Indexes on the Payments
table. The column LoanId should definitely be indexed.
Type is another candidate for indexing providing there is a
resonable number of possible values. Even creating a
dual-column index of LoanId and Type may yield some improvement

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
OK, that gives me the correct results. Now, to be picky,
Click to expand...
it takes about 15 seconds to run that query, which is fast
enough, I suppose, but seems slow. Any way to avoid that
performance hit?
 
Back
Top