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.
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
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
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"
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
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?