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: logIDFirstNameLastNameComments L12345JoeBloggs Some comments Sample data in the log_comments table: logIDFullComments L12345sample 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 returnjust 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]
RE: 'Select' statement question!
SELECT logs.firstname, logs.lastname, logs.reference, 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: logIDFirstNameLastNameComments L12345JoeBloggs Some comments Sample data in the log_comments table: logIDFullComments L12345sample 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 returnjust 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]
RE: 'Select' statement question!
SELECT logs.firstname, logs.lastname, logs.reference, 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]
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 cfoutputtd 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]
RE: 'Select' statement question!
It returns multiple records because you probably have multiple 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 cfoutputtd 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]
Re: 'Select' statement question!
You are a genius. I'm going home now but will try this tomorrow. Thanks again Pascal, don't know what I would have done without your help. Cheers, cfcoder [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: 'Select' statement question!
At 11:42 AM 5/21/2004, you wrote: The following code is in a stored procedure. Can somebody please just confirm if this is a valid SQL Statement. I appreciate this is not a SQL Forum. I'm a ColdFusion developer and am trying to query the db. SET @SQLStatement = @SQLStatement + 'SELECT * From Employee where Employee.asstetID IN (select distinct Employee.callNo from Employee where Employee.asst_id IN (select Assets.asst_id from Assets where bar_code = ' + char(39) + @asset + char(39) + '))' Thanks in advance May not be able to nest sub-queries. You using MS SQL Server? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: 'Select' statement question!
Sorry, I made a typo mistake. This is the correct sql SET @SQLStatement = @SQLStatement + 'SELECT * From Employee where Employee.callNo IN (select distinct Employee.callNo from Employee where Employee.asst_id IN (select Assets.asst_id from Assets where bar_code = ' + char(39) + @asset + char(39) + '))' I'll try and explain what I'm trying to do I have two tables. Employee and Assets The Employee Table has two columns: CallNo and Asst_id The Assets table contains all the asset information, the primary key being Asst_ID. I want to return distinct Employee records. The CallNo column in the employee table can more than one callNo with the same value. Ex: Employee Table: Asst_IDCallNo 10BRC 20BRC 30BRC0001 I want to only return distince employee records. Hope this is making sense The following code is in a stored procedure. Can somebody please just confirm if this is a valid SQL Statement. I appreciate this is not a SQL Forum. I'm a ColdFusion developer and am trying to query the db. SET @SQLStatement = @SQLStatement + 'SELECT * From Employee where Employee.asstetID IN (select distinct Employee.callNo from Employee where Employee.asst_id IN (select Assets.asst_id from Assets where bar_code = ' + char(39) + @asset + char(39) + '))' Thanks in advance __ Do you Yahoo!? Yahoo! Domains Claim yours for only $14.70/year http://smallbusiness.promotions.yahoo.com/offer [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: 'Select' statement question!
yes I am using MS SQL Server [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: 'Select' statement question!
cf coder wrote: The following code is in a stored procedure. Can somebody please just confirm if this is a valid SQL Statement. I appreciate this is not a SQL Forum. I'm a ColdFusion developer and am trying to query the db. SET @SQLStatement = @SQLStatement + 'SELECT * From Employee where Employee.asstetID IN (select distinct Employee.callNo from Employee where Employee.asst_id IN (select Assets.asst_id from Assets where bar_code = ' + char(39) + @asset + char(39) + '))' I don't think it is valid because the use of Employee in the predicate of the subquery is ambiguous. Further the DISTINCT is superfluous. Try something like: SELECT * FROM Employee E1 WHERE E1.asstetID IN ( SELECT E2.callNo FROM Employee E2 WHERE E2.asst_id IN ( SELECT Assets.asst_id FROM Assets WHERE bar_code =' + char(39) + @asset + char(39) + ' ) ) I have no idea about all the + and char() functions because that obviously is not standard SQL and you didn't tell which DBMS you are using. But I would flatten out the subqueries and rewrite the whole thing as: SELECT E1.* FROM Employee E1 INNER JOIN Employee E2 ON E1.AsstetID = E2.callNo INNER JOIN Assets A ON E2.asst_id = A.asst_id WHERE A.bar_code =' + char(39) + @asset + char(39) + ' Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: 'Select' statement question!
Thanks Jochem, but your sql returns duplicate callNo's. I tried both your queries. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: 'Select' statement question!
cf coder wrote: Thanks Jochem, but your sql returns duplicate callNo's. I tried both your queries. Then please elaborate on your problem and the schema of your database. What I have so far is: - you have a fragment of a bar-code - that fragment identifies assets - ??? - employee records Obviously I am missing the way you want to connect your assets to your employee records. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
