Database "OR" problems

[kreut]

Hello!

I was hoping that someone could help me with an "OR" query that's not working as planned. Essentially, I'm try to pick off correctly answered questions (submitted_homework table) from a quiz tied to my data base OR questions that students have a chance to try again from a second table (understood_questions). If I run the second part of the OR, it gives me back the correct id numbers, but if I run the query as is, it just spits back all of the question information from the first half of the query as opposed to all question information using the question_id's from both the first part of the OR statement and second part of the OR statement. Any help would be appreciated...

Thank you,

Eric

SELECT *  //get all the question info such as question id, text, solution
			FROM questions
			INNER JOIN submitted_homework
			USING (question_id)
			WHERE submitted_homework.assignment_id = $assignment
			AND submitted_homework.user_id = $student
			AND submitted_homework.submitted_solution = questions.solution  //first part does as it should
			OR question_id IN  //trying to use these question_ids in this
                                                 //table as well but it's a no go!
			(SELECT question_id
			FROM understood_questions
			WHERE understood_questions.user_id=$student
			AND understood_questions.assignment_id=$assignment

[r937]

SELECT questions.* 
  FROM submitted_homework
INNER 
  JOIN questions
    ON questions.question_id = submitted_homework.question_id
   AND questions.solution    = submitted_homework.submitted_solution
 WHERE submitted_homework.assignment_id = $assignment
   AND submitted_homework.user_id = $student
UNION
SELECT questions.* 
  FROM understood_questions
INNER
  JOIN questions
    ON questions.question_id = understood_questions.question_id
 WHERE understood_questions.assignment_id = $assignment
   AND understood_questions.user_id = $student

[kreut]

Thanks (again); and BTW I just bought your book today. It looks just as clear as your answers here and I'm looking forward to diving in.

-Eric

[r937]

thank you for the very kind words

if you have any questions about the book, please do post them in the SitePoint Books Questions forum