List members and their most recent dues payments 1

[AndrewRansom]

Hi,

Given two tables:
1: tMembers (fields are MemberID,FirstName,LastName,Email)
2: tDues (fields are DuesID,MemberID,PaymentDate,Amount,HasPaid)

MemberID in tDues is a foreign key to MemberID in tMembers

I'd like to generate a query that shows all members (once only, but show them even if no payment has ever been made) and their most recent payment date and amount. I've tried various combinations of joins, max() funcs, and grouping, but the best I've been able to do is to have multiple rows for the same member if they've made more than one payment.

Advice would be much appreciated!

 

[PHV]

Perhaps something like this ?
SELECT A.MemberID,A.FirstName,A.LastName,A.Email,B.PaymentDate,B.Amount
FROM tMembers AS A LEFT JOIN (
SELECT D.MemberID,D.PaymentDate,D.Amount
FROM tDues AS D INNER JOIN (
SELECT MemberID,MAX(PaymentDate) AS LastDate FROM tDues GROUP BY MemberID
) AS L ON D.MemberID=L.MemberID AND D.PaymentDate=L.LastDate
) AS B ON A.MemberID=B.MemberID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AndrewRansom]

Thanks very for the quick reply, PHV!

Your suggestion worked like a charm! I suspected that the solution required a combination of nested joins and derived tables, but just couldn't wrap my head around the SQL required.