|
After about 30 seconds of thought this is what I
came up with:
SELECT COUNT(testQuestions.questionID) AS outOf,
(SELECT Count(answers.answerID)
FROM answers, userAnswers,
testQuestions
WHERE answers.answerID = userAnswers.answerID
AND
userAnswers.memberID = 1
AND testQuestions.questionID = answers.questionID
AND testQuestions.testID = 1
AND answers.correct = 1) AS
rightAnswers
FROM testQuestions
WHERE testQuestions.testID =
1
returns:
3,2
Anybody have a cleaner faster way of doing
that?
Adaryl "Did you reboot?" Wakefield
Aviator by passion
Programmer by
sheer force of will
----- Original Message -----
Sent: Wednesday, February 18, 2004 12:39
PM
Subject: Re: [KCFusion] SQL
question
Ok i had to chew on that for a second. I
currently had my test user scoreing 0/10 and I was getting
no records back, which is actually right it just took a second to realize what
I was looking at. This query is actually altered from something
similar I wrote three days ago. They both do similar things but it looks like
I may have to just rethink this whole query. I had that outer join in
there cause it was necessary to count how many questions there were (the
denominator for the score calculation in other words) but I may be
able to do that more effectively another way.....
Adaryl "Did you reboot?" Wakefield
Aviator by passion
Programmer by
sheer force of will
----- Original Message -----
Sent: Wednesday, February 18, 2004
11:50 AM
Subject: RE: [KCFusion] SQL
question
SELECT answers.answerID, answers.answerLetter,
userAnswers.answerID as userAnswer, answers.correct, answers.feedback,
testQuestions.questionID, testQuestions.question,
testQuestions.questionNumber, answers.answer
FROM answers, userAnswers,
testQuestions
WHERE answers.answerID = userAnswers.answerID
AND
userAnswers.memberID = 1
AND testQuestions.questionID =
answers.questionID
AND testQuestions.testID = 1
AND answers.correct =
1
for the one user with memberid = 1, you are going to get all of their
correct answers for the testid = 1. I don't see any reason for an outer
join. and when you're putting in that outer join, even though you're putting
a where <> null in the where statement, that is only for the row
selection part of the query, which might or might not knock out some rows,
but with the outer join, you're just going to put nulls right back in, which
is why you're seeing nulls, and I'm betting it's one row of data for each
answer to the test, whether they answered it correctly or not, which would
give you a null for userAnswer if they did not choose that
answer.
Bruce Dunwiddie
Ticket Technology
P:
866.543.3331
F: 913.451.7832
[EMAIL PROTECTED]
testQuestions(questionID PK, testID FK,
questionNumber, question)
answers(answerID PK, questionID FK, answer,
answerLetter, feedback, correct)
userAnswers(userAnswerID PK, questionID FK,
answerID SK, memberID SK)
Im trying to get all the correct answers from
a test. correct is a boolean and set to 1 if the answer is right. a single
row of each table would look like
testQuestions:
1, 1, 1, 'Why is the sky blue'
answers
1, 1, 'Beause of a concept called Reighlei
scattering', 'a', 'This is the best answer', 1
userAnswers
1, 1, 1, 1
Adaryl "Did you reboot?" Wakefield
Aviator by
passion
Programmer by sheer force of will
----- Original Message -----
Sent: Wednesday, February 18, 2004
11:31 AM
Subject: RE: [KCFusion] SQL
question
I think it's your outer join getting in the way. why don't you
give us a run down of the tables, and what you're trying to accomplish
with the outer join. I can then recreate the tables on my side and get
it to work.
Bruce Dunwiddie
Ticket Technology
P: 866.543.3331
F:
913.451.7832
[EMAIL PROTECTED]
No dice. No matter what combination of
logic I use, userAnswer is an invalid column and userAnswers.answerID
is just not recognized as being in the result set.
Adaryl "Did you reboot?" Wakefield
Aviator by
passion
Programmer by sheer force of will
----- Original Message -----
Sent: Wednesday, February 18,
2004 11:14 AM
Subject: RE: [KCFusion] SQL
question
Try
AND
userAnswers.answerID is not
null
also you
may have case as well.
AND
userAnswers.answerID <>
''
From: Adaryl
"Bob" Wakefield [mailto:[EMAIL PROTECTED]
Sent:
Wednesday, February 18, 2004 11:05 AM
To: [EMAIL PROTECTED]
Subject:
Re: [KCFusion] SQL question
Sorry Ryan that last line is a typo im
playing with two differnt versions the version you suggest
nate:
SELECT answers.answerID,
answers.answerLetter, userAnswers.answerID as userAnswer,
answers.correct, answers.feedback, testQuestions.questionID,
testQuestions.question, testQuestions.questionNumber,
answers.answer
FROM answers, userAnswers, testQuestions
WHERE
answers.answerID *= userAnswers.answerID
AND userAnswers.memberID
= 1
AND testQuestions.questionID = answers.questionID
AND
testQuestions.testID = 1
AND answers.correct = 1
AND
userAnswers.answerID <> NULL
This returns tuples where userAnswer is
actually NULL
the other version
AND userAnswer <> NULL gives me
invalid column name 'userAnswer'
Im using the query analyser in SQL
Server 2K
Adaryl "Did you reboot?" Wakefield
Aviator by
passion
Programmer by sheer force of will
----- Original Message -----
Sent: Wednesday, February 18,
2004 10:55 AM
Subject: RE: [KCFusion] SQL
question
I have found that filtering (whether in a WHERE clause or
GROUP BY or ORDER BY) using an alias is unreliable at best. As a
matter of habit now, I always use the fully qualified _expression_
instead.
In your example, your last line would be "AND
userAnswers.answerID <> NULL"
Nate
-----Original
Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Adaryl
Wakefield
Sent: Wednesday, February 18, 2004 10:48
AM
To: [EMAIL PROTECTED]
Subject:
[KCFusion] SQL question
Ok ive spent a half hour on this so
I think im safe asking a question
I have this monster
SELECT answers.answerID,
answers.answerLetter, userAnswers.answerID AS userAnswer,
answers.correct, answers.feedback, testQuestions.questionID,
testQuestions.question, testQuestions.questionNumber,
answers.answer
FROM answers, userAnswers,
testQuestions
WHERE answers.answerID *=
userAnswers.answerID
AND userAnswers.memberID = 1
AND
testQuestions.questionID = answers.questionID
AND
testQuestions.testID = 1
AND answers.correct =
1
AND userAnswer AS <> NULL
Its the last line that is giving me
trouble it does not seem to like to filter on a
alias.
Adaryl "Did you reboot?"
Wakefield
Aviator by passion
Programmer by sheer force of
will
|
