You still should have some sort of relationship between the 2 tables
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Knott,
Brian
Sent: Monday, June 30, 2003 12:40 PM
To: CFAussie Mailing List
Subject: [cfaussie] RE: SQL Questions
Steve,
You have missed the point. I'm only working with 2 tables, topics
and answers.
There is no question table. The assessments are flash object and the
questions will never change, so no question details are stored. All I need
is the total number of questions.
The topic table has an ID.
The answer table has an answer id, a topic id, a userid , and a question
number.
I send the flash object a topic ID, a user ID and the questions to be
attempted.
The flash object then returns the topic ID, the User ID, and the answers
(yes or no) from the questions attempted. All this data is saved into the
answers database.
The next time the user does the assessment I run a query (the one in this
thread) on the answers table to find out which question have not been
answered correct, these are passed down to the flash object. The questions
are always the same. A user sits the same assessment and thus questions
until he has got all questions correct.
Brian
-----Original Message-----
From: Steve Onnis [mailto:[EMAIL PROTECTED]
Sent: Monday, 30 June 2003 12:25 PM
To: CFAussie Mailing List
Subject: [cfaussie] RE: SQL Questions
Brian
Thats bad for a start.
In your case, the senario should go like this
Topics have a topic id
Questions have a question id and a topic id
The topic id for the question is related to the topic id in the topics table
Then the answers have an answer id and a question id
The question id for the answer is related to the question id in the
questions table
This is pretty basic relational database stuff
What happens if someone deletes a question and then adds one back in? You
will still have the same number of questions to loop through, but the link
between the questions and the answers will be all screwed up.
I would look at changing your DB structure. In the long run it will save
you lots of heartache
Steve
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Knott,
Brian
Sent: Monday, June 30, 2003 12:12 PM
To: CFAussie Mailing List
Subject: [cfaussie] RE: SQL Questions
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/
---
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/
