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.
>
> Here is the description of the database schema.
>
> The 'logs' table contains most of the information: Comments,
> Title, UserName etc. 'LogID' column is the primary key in the
> logs table.
>
> The 'log_comments' table has the full comment. The
> log_comments table references the 'logID' column in the logs table.
>
> Columns in the log_comments Table:
> FullComments, LogID
>
> The Assets are stored in the 'Assets' Table (asst_id is the
> primary key). log_assets table has two columns:
> logID and asst_id
>
> Here is the select statement.
>
> select logs.firstname, logs.lastname, logs.reference,
> logs.location, logs_comments.fullcomments <CFIF
> len(form.asset) 0 and len(form.sel_select2) gt 0> ,
> log_Assets.logID, log_Assets.asst_id </cfif> FROM logs FULL
> OUTER JOIN log_comments ON log_comments.LogID = Logs.LogID
> <CFIF len(form.test) gt 0 and len(form.select_tested) gt 0>
> FULL OUTER JOIN log_Assets ON log_Assets.logID = logs.logID
> </CFIF> WHERE 1 = 1
>
> <!--- If searching on logID --->
> <CFIF len(form.ref) gt 0>
> AND logs.logID = '#form.ref#'
>
> <!--- 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 log_assets.asst_id IN (select asst_id from Assets where
> tested_by LIKE '%#form.test#%')
>
> <CFELSEIF form.select_tested eq 'test period'>
> AND log_assets.asst_id IN (select asst_id from Assets where
> test_period = '#form.test#')
>
> </CFIF>
> </CFIF>
>
> If a user enters a logID in the form input text field (ref),
> the search brings back the record from the logs table and any
> comments from the log_comments table. It works fine.
>
> However, if the user selects an option from the select_tested
> drop-down, say 'tested by', inputs the username in the 'test'
> input form field, the search sometimes returns duplicate log
> records or in other words, the resultset sometimes contains
> more than one occurance of the same log record.
>
> Let me explain this in detail.
>
> Here is some Sample data in the logs table:
> logID FirstName LastName Comments
> L12345 Joe Bloggs Some comments
>
> Sample data in the log_comments table:
> logID FullComments
> L12345 sample full comments blah blah blah
>
> Sample data in the log_assets table:
> LogID asst_id
> L12345 1
> L12345 2
> L12345 3
> L12345 4
>
> Sample data in the assets table:
> asst_id tested_by test_period
> 1 testUser1 30/01/2005
>
> I want the search to return just one result for the above
> data, but it retuns 4 records. What am I doing wrong? I know
> the sql is not right, but I don't know how to properly code
> it. I'll be more than happy to answer any questions you might have.
>
> Best Regards,
> cf coder
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
