SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
M.zip, M.memberID, M.memberLevelID, max(T.paidThru)
FROM Membership M JOIN Transaction T ON M.memberID = T.memberID
GROUP BY M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
M.zip, M.memberID, M.memberLevelID
----- Original Message -----
From: Mark Leder
To: CF-Talk
Sent: Tuesday, June 15, 2004 11:15 AM
Subject: SQL Max Statement Help
Hi All,
Two tables I'm trying to query:
1) Membership table, each with a unique "memberID"
2) Transaction table, could be many records associated with one member ID
(one-to-many). Has a unique ID (transaction ID, along with "memberID"
column(for joining) and a "paidThru" column.
I want to query so that each memberID record will only call the most recent
"paidThru". Trouble I'm having is I can't get the SELECT version to work at
all, the WHERE version brings back the correct record count, but I can't
display the "PaidThru" either as a true column name, nor as an alias.
Any ideas on how to get either one of these to work?
Here's the select statement version: =====
SELECT M.firstName, M.middleName, M.lastName, M.email, M.company, M.city,
M.zip, M.memberID, M.memberLevelID, T.memberID, T.paidThru, (SELECT
MAX(T.paidThru) FROM #REQUEST.prefix#_Members_TransactionLog T) AS paidThru
FROM #REQUEST.prefix#_Members_List M,
#REQUEST.prefix#_Members_TransactionLog T
WHERE M.memberID = T. memberID
Here's the WHERE statement version: =====
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 EXISTS (SELECT MAX(paidThru) AS paidThruDate, memberID FROM
#REQUEST.prefix#_Members_TransactionLog WHERE memberID = M.memberID GROUP BY
memberID)
Thanks, Mark
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
