logs.location, logs_comments.fullcomments
FROM logs LEFT OUTER JOIN log_comments ON log_comments.LogID =
Logs.LogID
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 search on assets, you were selecting the asset_id, so it is
normal you get multiple records. If you don't need info on the assets,
keep it in the subselect.
You will still have more than one record with the same LogId if the
logID is related to multiple full comments! You can't avoid that, since
you are selecting "fullcomments".
You can take care of it in the output by using <cfoutput
group="logId"...> . Don't forget "ORDER BY LogID" if you want to do
this.
Pascal
> -----Original Message-----
> From: cf coder [mailto:[EMAIL PROTECTED]
> Sent: maandag 24 mei 2004 13:03
> To: CF-Talk
> Subject: Re: 'Select' statement question!
>
> I'll be more specific this time. Sorry! Ok! I am working on
> the search functionality. The search should allow users to
> find a log from the logs table and also allow users to find
> logs that are attached to assets.
>
> Users can search by log/call ref no by inputting the log no
> in a textfield or by selecting an option from the asset drop-down.
> The asset-drop down has 2 options: tested by, test period.
> The user can select an option and enter the value in the text
> field provided next to it.
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
