Brian, I think you need to look at the IN keyword and sub queries, if your using SQL Server use the help doco's that come with it, they'll give you all the info you need to know about the IN keyword and even examples.
i.e. SELECT * FROM blah WHERE id IN (SELECT id FROM blah2 where this = that) -----Original Message----- From: Knott, Brian [mailto:[EMAIL PROTECTED] Sent: Monday, 30 June 2003 10:08 AM To: CFAussie Mailing List Subject: [cfaussie] SQL Questions I'm trying to get a bit smarter with my SQL statements so I'm after the most efficient method of doing the following. This is an online exercise where the user sits topics. the answers are recorded in a answer table. The topic table contains the number of questions in a topic , eg 10. The first time a user does a topic they are presented with all questions, the second time they are only presented with the questions they got wrong, and so on until they get all question correct. So what I need to do is create a query to look at the number of questions , eg 10, then look at the answer table and return if that question exists and if so was it correct. I could do it by looking at the number of questions and then looping through each to see if it exists but I would like to know if it can be done in SQL in one statement. Below is an example of how I would do it normally. <cfquery name="questions'> Select number_of_questions from topic where ID = XXX </cfquery> <cfloop query'"questions"> <cfquery name="list_of_questions"> select question_number from answers where topic_id = XXX and question_number = #questions.number_of_questions# and answer = 'yes' </cfquery> <cfif list_of_question.recordcount EQ 0> <cfset variables.list = variables.list & ',' & '#list_of_questions.question_number#'> </cfif> </cfloop> Brian Knott QANTM Studio Senior Programmer Ph (07) 30174331 Mob 0407572127 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ ---------------------------------------------------------------------------- This email, together with any attachments, is intended for the named recipient(s) only and may contain privileged and confidential information. If received in error, please inform the sender as quickly as possible and delete this email and any copies from your computer system network. If not an intended recipient of this email, you must not copy, distribute or rely upon it and any form of disclosure, modification, distribution and/or publication of this email is prohibited. Unless stated otherwise, this email represents only the views of the sender and not the views of the Queensland Government. ---------------------------------------------------------------------------- --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
