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]
