"fullcomments" in the log_comments table (you are doing a join!). Do you
really need those? Otherwise drop the table alltogether in your query.
SELECT logs.firstname, logs.lastname, logs.reference, logs.location
FROM logs
WHERE 1 = 1
<!--- If searching on logID --->
<cfif len(form.ref) gt 0>
AND logs.logID = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form.ref#">
</cfif>
<!--- If searching on tested by, test period--->
<cfif len(form.select_tested) gt 0 and len(form.test) gt 0>
<cfif (form.select_tested eq 'tested by'>
AND logs.logID IN (
SELECT LogID
FROM log_assets INNER JOIN assets ON log_assets.asst_id =
assets.asst_id
WHERE assets.tested_by LIKE <cfqueryparam
cfsqltype="CF_SQL_VARCHAR" value="%#form.test#%">
)
<cfelseif form.select_tested eq 'test period'>
AND logs.logID IN (
SELECT LogID
FROM log_assets INNER JOIN assets ON log_assets.asst_id =
assets.asst_id
WHERE assets.test_period LIKE <cfqueryparam
cfsqltype="CF_SQL_VARCHAR" value="%#form.test#%">
)
</cfif>
</cfif>
If you need it, you can calculate the total on the fly:
<cfset total = 0>
<cfsavecontent variable="tmp">
<cfoutput group="logID" query="searchresults">
<cfset total = total + 1>
blah blah blah
</cfoutput>
</cfsavecontent>
<cfoutput>
<tr>
<td class="title">#variables.total# logs found.</td>
</tr>
#variables.tmp#
</cfoutput>
> -----Original Message-----
> From: cf coder [mailto:[EMAIL PROTECTED]
> Sent: maandag 24 mei 2004 14:09
> To: CF-Talk
> Subject: Re: 'Select' statement question!
>
> Many thanks Pascal for that. It still returns multiple
> records. I used the group by tag which stops this from
> happenning, so I don't see multiple records of the same log.
> Its all good. However, I'm displaying the recordcount in the
> search result and it doesn't give you the correct picture of
> the no of records returned and the actual display.
>
> <tr>
> <cfoutput><td class="title">#searchresults.recordcount# logs
> found.</cfoutput> </tr>
>
> <cfoutput group="logID" query="searchresults">
> blah blah blah
> </cfoutput>
>
> see what I'm saying?
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
