RE: [KCFusion] SQL question

[Bruce Dunwiddie]

Title: Message

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