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
