opps.. I hit send before fixing a problem in the query.. the subselect needed to be narrowed down by student id... here goes again:
select questionid from questions_in_quiz where quizid=$qid and questionid not in (select question_in_quiz_id from answered_questions,questions_in_quiz,student_in_class where questions_in_quiz.questionid=answered_questions.question_in_quiz_id and questions_in_quiz.quizid=$qid and answered_questions.studentclassid=student_in_class.studentclassid and student_in_class.studentid=$sid) -matt > On Tue, 5 Aug 2003, Michael Ryan Byrd wrote: > > > Warning: not for the SQL faint of heart. > > > > So I'm making a small quiz program with tables A-D described below: > > > > A.) table "questions" > > purpose: lists the questions > > format: [id,prompt] > > example: [1024, "how much wood could a woodchuck chuck?"] > > example meaning: there exists a question #1024 which is, "how much wood could a > > woodchuck chuch?" > > > > B.) table "questions_in_quiz" > > purpose: to 'tie' questions to a quiz > > format: [id, quizid, questionid] > > example: [10, 411,1024] > > example meaning: quiz #411 contains a question #1024 (you remember, about the > > woodchuck.) > > > > C.) table "answered_questions" > > purpose: tracks which questions a student has already answered > > format: [question_in_quiz_id, studentclassid] > > example: [1024, 2] > > example meaning: a student with a studentclass id of 2 already answered question > > 1024 > > > > D.) table "student_in_class" > > purpose: assigns a student into one or more classes > > format: [studentclassid, studentid, classid] > > example: [2,"123-45-6789", 5] > > example meaning: student with id 124-45-6789 has been enrolled into class #5 and > > given a studentclassid of 2. > > > > > > Goal: Obtain a list of all questions from a particular quiz, $qid, not > > already answered by a particular student, $sid. > > > > Currently, I do this in two queries:. > > > > First, find a list of all questions in quiz, $qid. > > > > SELECT questions_in_quiz.id as QUESTIONID, questions.prompt > > FROM questions_in_quiz > > LEFT JOIN questions > > ON questions_in_quiz.questionid = questions.id > > WHERE questions_in_quiz.quizid = $qid > > > > > > Second, for each row of table returned from first query, check to see if that > > QUESTIONID has been answered by student $sid. > > > > SELECT count(*) > > FROM answered_questions > > LEFT JOIN student_in_class > > ON answered_questions.studentclassid = student_in_class..studentclassid > > WHERE answered_questions.question_in_quiz_id = QUESTIONID > > AND student_in_class.studentid = $sid > > > > If the second query returns zero results, then the question QUESTIONID has not > > yet been answered by student $sid > > > > > > Question: Can I do this in MySQL with only ONE query? > > > > > > Ideas? > > > > > > ____________________ > > BYU Unix Users Group > > http://uug.byu.edu/ > > ___________________________________________________________________ > > List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list > > > > > ____________________ BYU Unix Users Group http://uug.byu.edu/ ___________________________________________________________________ List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list
