RE: [KCFusion] SQL question

[Smith, Jim]

Title: Message

I would try it without the alias and see what I get. I ran a test here to see if I could get results using the column name instead of the alias and it worked fine. Just a thought, are you sure userAnswers.answerID is the right name? -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Adaryl Wakefield Sent: Wednesday, February 18, 2004 11:23 AM To: [EMAIL PROTECTED] Subject: Re: [KCFusion] SQL question 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 ----- From: Moore, Paul To: '[EMAIL PROTECTED]' 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 ----- From: Nate Rightmire To: [EMAIL PROTECTED] 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