Joining 2 tables with SQL

Databases
1

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. :nono:

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 
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
3

Makes total sense… :slight_smile:

Thanks so much!

4 
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. :slight_smile:

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

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

what happened when you tested that?

:wink:

6

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

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

:slight_smile:

8

OK!

How about this then. :slight_smile:

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?:shifty:

-Eric

9

i’m not at all confused :slight_smile:

you’re gonna want to start building your query with this –

WHERE instructor_id = 72

which table has this information? apparently the courses table

that means we can expand our query-in-progress to this –

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 –

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?

10

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