Query Help Needed

[mjuni]

Here is my dilemma... I have two tables, the structures are as follows:

student_answers

student_id
question_id
answer_id

This table contains a student's answer for each question on a test.
A question may have one or multiple answers.

test_answers

test_id
question_id
answer_id

This table contains the possible correct answers for a test question.
*Note not all test questions are represented in this table as
some questions not count towards a grade.

I need to come up with a query that does the following:
1. Finds all possible answers for each question in test_answers.
2. For each question in test answers, finds the corresponding question
in student_answers and checks that at least one answer_id in students_answers
matches at least one answer_id in test answers for each question.
3. The query should then return the student_id that has at least one correct
answer for every question in test answers.

I am using MySQL and it does not support Subqueries.

Any ideas?

[zaichik]

Hmm, that is a sticky one. I can't think of how to do that without subqueries.

I guess that you would have to run separate queries, storing the resulting recordset in an array and then using array searching techniques. Would that work?

[amcgrath]

I'm not really sure if this would help you out, but you could run a query to get all the right answers. Then, run another query to get all the answers a student made. Then, using the array_diff() function, compare the two and you would have all the correct answers the user missed. Lemme know if that's what you intend to do. (It would be incorrect, however, if on multiple answer questions, that a user is graded as "correct" if they did not select all of the correct answers...)

amc