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/
