SitePoint Sponsor

User Tag List

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

    Joining 2 tables with SQL

    Hello!

    I was hoping that I could have some help writing an inner join that involves two tables; I can't seem to get the right syntax to make things work.

    Basically, I have the following tables:
    courses: course_id
    assignments_instructors: assign_id, course_id
    assignments_questions: assign_id, question_id

    Given a particular course (that's the course_id from my course_table), I want to get all questions that are in any of the assignments for a given course. Each question_id is unique and falls under an assign_id (unique and with multiple question_id's) while each assign_id fits into one of the course_id's (unique with multiple assign_ids). So essentially, I need to inner join courses with assignments_instructors with assignments_questions to pick off the correct questions. Any help on the syntax would be appreciated.

    Thank you,

    Eric

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT assignments_questions.question_id
      FROM assignments_instructors
    INNER
      JOIN assignments_questions
        ON assignments_questions.assign_id = assignments_instructors.assign_id
     WHERE assignments_instructors.course_id = 42
    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)
    Makes total sense....

    Thanks so much!

  4. #4
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT assignments_questions.question_id
    FROM assignments_instructors
    INNER
    JOIN assignments_questions
    ON assignments_questions.assign_id = assignments_instructors.assign_id
    WHERE assignments_instructors.course_id = 42
    After thinking through my query, I've realized that there's still another layer to go; I've been banging my head now for a few hours so any additional help would be appreciated.

    I now realize that I'd like the query at the instructor level as opposed to the course level; each instructor may teach several courses. So, if my assignments_instructors has course_id's associated with it and then each unique course_id is associated with possibly more than one instructor_id in my courses table, is there a way to make it so that my final WHERE is, something like "WHERE instructor_id=72"?

    Sorry for not thinking this through as thoroughly as I should have.

    -Eric

    Code:
    SELECT assignments_questions.question_id
    FROM assignments_instructors
    INNER
    JOIN assignments_questions
    ON assignments_questions.assign_id = assignments_instructors.assign_id
    WHERE assignments_instructors.course_id = "all the possible courses for a given instructor"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kreut View Post
    ... is there a way to make it so that my final WHERE is, something like "WHERE instructor_id=72"?
    what happened when you tested that?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Your original reply worked great (for what it was supposed to do)...my own attempts have brought several calls by the police.

    Any additional help would be appreciated in helping me to write my first (who-what-where-sort of join).

    -Eric

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    so you want the query at the instructor level, but unfortunately, after a thorough review of the information you posted in post #1, it appears that you don't have an instructor column anywhere, so i am afraid i won't be able to help you

    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)
    OK!

    How about this then.

    On my assignments_instructors table I've got both assignment_id and course_id. Then, the course_id's are on the courses table which have, you guessed it, course_id and (don't be shocked!), instructor_id. There can be several assignment_id's matched up to a particular course_id and several course_id's matched up to a particular instructor_id. So, the query maze which I'm trying to pass through is to find all questions from a given instructor, where I need to go through the hoops of questions within assignments within courses for a particular instructor.

    Does that help or are you now as confused as I am?

    -Eric

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i'm not at all confused

    you're gonna want to start building your query with this --
    Code:
    WHERE instructor_id = 72
    which table has this information? apparently the courses table

    that means we can expand our query-in-progress to this --
    Code:
    SELECT ...
      FROM courses
     WHERE courses.instructor_id = 72
    which other table(s) does the courses table relate to? why, the assignments table

    that means we can expand our query-in-progress to this --
    Code:
    SELECT ...
      FROM courses
    INNER
      JOIN assignments
        ON assignments.course_id = courses.course_id
     WHERE courses.instructor_id = 72
    can you see what's happening here?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    With a bit of tweaking I was able to fully integrate your suggestions into my query. It works perfectly!

    Thank so much for your patience and willingness to help. I look forward to ordering your SQL book.

    -Eric


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
  •