SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Database "OR" problems

    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
    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •