I don't have a list of questions, I just know there is 10 questions in topic 1. So I can't look at the topic table and get the question numbers, but I can get the total number of questions. So the topic query would return the number 10, I then need to look in the answer table for all the questions number from 1 to the number of question, in this case 10.
I'm presently doing it by querying the topics table to get the number of question, then looping from 1 to the number of questions and querying the answer table with the index value (question number). I'm just trying to see if I can condense it into 1 query. Brian Knott -----Original Message----- From: Steve Onnis [mailto:[EMAIL PROTECTED] Sent: Monday, 30 June 2003 11:59 AM To: CFAussie Mailing List Subject: [cfaussie] RE: SQL Questions Why would you even use IN? <cfquery name="questions'> SELECT Answers.question_number FROM Topic,Answers WHERE Topic.TopicID = XXX AND Answers.TopicID = Topic.TopicID AND Answers.QuestionNumber = Topics.QuestionNumber </cfquery> If yout DB is set up right, that should do the same thing. Now, the issues come if your holding a list in the topics table to hold the list of questions. Is this is the case, neither the above methos will work nor will IN. SELECT * FROM blah WHERE id IN (SELECT id FROM blah2 where this = that) When you do the subselect for the IN clause, the value returned will be string value of the list, not a list of IDs. The query will error as you are trying to compare a string value with an INT value. If your trying to get smarter with your SQL queries, I would suggest starting from the ground up. Look into DB design and make sure your creating your databases well. Thats the best place to start. Steve -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Taco Fleur Sent: Monday, June 30, 2003 10:16 AM To: CFAussie Mailing List Subject: [cfaussie] RE: SQL Questions 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/ --- 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/ --- 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/
