I think Sasha will shoot me for sugesting this, but: use a subselect and "NOT IN" statement.
select questionid from questions_in_quiz where quizid=$qid and questionid not in (select question_in_quiz_id from answered_questions,questions_in_quiz where questions_in_quiz.questionid=answered_questions.question_in_quiz_id and questions_in_quiz.quizid=$qid) and like I say folks, if mysql is not fast enough for ya, pay mucho dinero for Oracle. :)... Oracle *does* support the nesting of selects (subselects) up to 99 levels deep. now that would be one complex query! -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
