|
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
|
