many transactions (one to many relationship). I want to retrieve each member
ID, and their corresponding most recent (MAX) transactionID. The memberID
joins the two tables.
I've tried several ways without success. The statement below below returns
ALL the transaction records for any given individual member, not just the
most recent transactionID. What am I doing wrong?
SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
M.zip, M.memberID, M.memberLevelID
FROM #REQUEST.prefix#_Members_List M
WHERE M.memberID =
(SELECT MAX(T.transactionID) AS transactionID, T.paidThru,
T.transactionDate, T.memberID)
FROM #REQUEST.prefix#_Members_TransactionLog T
WHERE T.memberID = M.memberID) AND
...more filters ...
GROUP BY M.memberID, M.firstName, M.middleName, M.lastName, M.email,
M.company, M.city, M.zip, M.memberLevelID, T.paidThru, T.transactionDate,
T.memberID
ORDER BY M.lastName
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
