'Select' statement question!

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

At 11:42 AM 5/21/2004, you wrote: ----- Excess quoted text cut - see Original Post for more ----- May not be able to nest sub-queries. You using MS SQL Server?

yes I am using MS SQL Server

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_ID   CallNo 1         0BRC0000 2         0BRC0000 3         0BRC0001 I want to only return distince employee records. Hope this is making sense ----- Excess quoted text cut - see Original Post for more -----

cf coder wrote: ----- Excess quoted text cut - see Original Post for more ----- 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

Thanks Jochem, but your sql returns duplicate callNo's. I tried both your queries.

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

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

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 ----- Excess quoted text cut - see Original Post for more -----

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 ----- Excess quoted text cut - see Original Post for more -----

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?

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> ----- Excess quoted text cut - see Original Post for more -----

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