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/
