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