SitePoint Sponsor

User Tag List

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

    Inner Join then left join

    Hello,

    I have 2 tables that I'd like to perform a combo inner join/left join on and can't quite get the syntax correct.
    My first join is:
    Code MySQL:
    SELECT questions.text,assignments_questions.question_id
    FROM questions
    INNER JOIN assignments_questions ON questions.question_id =assignments_questions.question_id
    WHERE assignment_id=208
    Then, in a second table, I have question_id's and submitted_solutions. There may or may not be a submitted solution: regardless the result should show up so that I need a left join. In other words, my final result could look like:

    Text ID Submitted solution
    A 1 True
    B 2
    C 3 Blah Blah

    Any help in getting the two joins to work together would be appreciated.

    Thanks!

    -Eric

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,250
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Something like this should work:

    Code MySQL:
    SELECT questions.TEXT
         , assignments_questions.question_id
         , assignments_solutions.submitted_solution
      FROM questions
     INNER JOIN assignments_questions ON questions.question_id = assignments_questions.question_id
      LEFT JOIN assignments_solutions ON questions.question_id = assignments_solutions.question_id
     WHERE assignment_id = 208
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for such a quick response! It's almost what I'm looking for. Is there any way to add an "AND" statement so that it ONLY applies to the 2nd LEFT JOIN table. For example, there could be many users submitting questions. If I could add "AND submitted_homework.user_id = 61" so that it only applies to the LEFT JOIN piece that would do it. Right now, if I write AND submitted_homework.user_id = 61, then it only produces the text for questions submitted by user_id 61. I'd actually like all of the question text for a given assignment, by a given user regardless of whether that user submitted a solution.

    Here's the current state of things:

    Code MySQL:
    SELECT questions.text
         , assignments_questions.question_id
         , submitted_homework.submitted_solution
      FROM questions
     INNER JOIN assignments_questions ON questions.question_id = assignments_questions.question_id
      LEFT JOIN submitted_homework ON questions.question_id = submitted_homework.question_id
     WHERE assignments_questions.assignment_id = 208

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,250
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by kreut View Post
    Is there any way to add an "AND" statement so that it ONLY applies to the 2nd LEFT JOIN table.
    Yup....all you do is add it to the join condition.

    Code MySQL:
    SELECT questions.text
         , assignments_questions.question_id
         , submitted_homework.submitted_solution
      FROM questions
     INNER JOIN assignments_questions ON questions.question_id = assignments_questions.question_id
      LEFT JOIN submitted_homework ON questions.question_id = submitted_homework.question_id AND submitted_homework.user_id = 61
     WHERE assignments_questions.assignment_id = 208
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Can't believe I didn't try that!

    Thanks so much.....

    -Eric

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,250
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by kreut View Post
    Can't believe I didn't try that!

    Thanks so much.....

    -Eric
    Sometimes the easiest answers are the hardest to see.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    can i make a suggestion?

    when you have a WHERE clause like this --
    Code:
    WHERE assignments_questions.assignment_id = 208
    then it's a good idea to start off your FROM clause with that table --
    Code:
      FROM assignments_questions 
    INNER 
      JOIN questions 
        ON questions.question_id = assignments_questions.question_id
    now, the mysql optimizer is pretty smart, and for inner joins, it's gonna figure out that the fastest results will be obtained by

    1. retrieve only the assignments_questions rows for the given assignment, then join to the questions table get the related questions

    rather than

    2. retireve all questions, join to all matching assignments_questions rows, then throw away the ones that aren't for assignment 208

    see the difference?

    like i said, mysql is smart enough to figure out that it should execute 1 instead of 2 (when it's an inner join)

    with this in mind, i find that it makes ~way~ more sense to actually write the FROM clause the same way

    why?

    because if you are looking at a strange query, trying to understand what it's doing (and often the author of the query is usually yourself, and you're trying to enhance it a couple months down the road), it's a ~lot~ easier to figure it out if it's written in such a way as to model what the query is actually trying to accomplish

    i call the first table in the FROM clause the "driving" table, as it is what drives the successful and efficient retrieval of rows, and it is almost always the one that has the associated WHERE condition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Great suggestion! Thanks so much. I didn't realize that SQL implemented inner joins in such a way.


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
  •