retrieving just the highest numbered transaction ID and corresponding data
for each member. FYI - the filtering statements are only invoked after
someone does a new search from a form. The inital results, which is what I
am after, uses the <cfcase value = "all fields">. The default URL.sortorder
is "M.lastname" and URL.sortDir is "ASC".
Thanks for helping me with this.
===================
<cfquery name="qmemberList" datasource="#REQUEST.dsnSQL#"
username="#REQUEST.dsnUID#" password="#REQUEST.dsnPWD#"
CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#">
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 TOP 1 T.transactionID, T.paidThru, T.transactionDate, T.memberID
FROM #REQUEST.prefix#_Members_TransactionLog T) AND
<!--- If the user provided a filter string, --->
<cfswitch _expression_="#SESSION.memberList.fieldSelect#">
<cfcase value="All Fields">
(M.firstName <cfif #SESSION.memberList.searchModifier# IS
"containing">LIKE '%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
OR M.lastName <cfif #SESSION.memberList.searchModifier# IS
"containing">LIKE '%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#'</cfif>
OR M.email <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#'</cfif>
OR M.company <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#'</cfif>
OR M.city <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#'</cfif>
OR M.zip <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#'</cfif>)
</cfcase>
<cfcase value="First Name">
AND M.firstname <cfif #SESSION.memberList.searchModifier# IS
"containing">LIKE '%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
</cfcase>
<cfcase value="Last Name">
AND M.lastname <cfif #SESSION.memberList.searchModifier# IS
"containing">LIKE '%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
</cfcase>
<cfcase value="Email address">
AND M.email <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
</cfcase>
<cfcase value="company">
AND M.company <cfif #SESSION.memberList.searchModifier# IS
"containing">LIKE '%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
</cfcase>
<cfcase value="city">
AND M.city <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
</cfcase>
<cfcase value="zip">
AND M.zip <cfif #SESSION.memberList.searchModifier# IS "containing">LIKE
'%#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "beginning
with">LIKE '#SESSION.memberList.ffText#%'
<cfelseif #SESSION.memberList.searchModifier# IS "equal to">=
'#SESSION.memberList.ffText#' </cfif>
</cfcase>
</cfswitch>
<!--- Filter on Criteria C (member status) if provided (querySwitch
13)--->
<cfif SESSION.memberList.FilterC NEQ "All">
AND '#SESSION.memberList.FilterC#' = M.memberLevelID
</cfif>
<!--- Also filter on From date, if provided --->
<cfif SESSION.memberList.FilterC NEQ "G">
<cfif IsDate(SESSION.memberList.DateFrom)>
AND T.paidThru >= #CreateODBCDate(SESSION.memberList.DateFrom)#
</cfif>
<!--- Also filter on Through date, if provided --->
<cfif IsDate(SESSION.memberList.DateThru)>
AND T.paidThru <= #CreateODBCDate(SESSION.memberList.DateThru)#
</cfif>
<cfelseif SESSION.memberList.FilterC EQ "G">
<cfif IsDate(SESSION.memberList.DateFrom)>
AND T.transactionDate >= #CreateODBCDate(SESSION.memberList.DateFrom)#
</cfif>
<!--- Also filter on Through date, if provided --->
<cfif IsDate(SESSION.memberList.DateThru)>
AND T.transactionDate <= #CreateODBCDate(SESSION.memberList.DateThru)#
</cfif>
</cfif>
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
<!---- Determines the sort column of the output
ORDER BY #URL.sortOrder# #URL.sortDIR# --->
</cfquery>
_____
From: Alexander Sherwood [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 18, 2004 10:23 AM
To: CF-Talk
Subject: RE: SQL Query problem
At 10:14 AM 8/18/2004, you wrote:
>MemberID from the members table corresponds to the memberID in the
>transaction table. So I must be writing the join wrong?
Seems that way. Post the whole query, just as you have it the code.
--
Alex
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
