Ok i'm trying to build a sql query that pulls two names of users from 1 user table to matchup two users (instructor and teaching assistant) in the same record of another table but can't get it right. Below is a simplified version of what i'm trying to do. I have a table "Courses" that stores the ID of an Instructor and a Teaching Assistant assigned to the course. Both the instructor name and teaching assistant name are pulled from the same table (as teaching assistants could technically be instructors as well). The problem is i can't create a query that pulls that information. Here's a simplified version of what i have. Any suggestions?
users
------
userID
Name
Courses
--------
courseID
CourseName
InstructorID
TeachingAssistantID
SELECT Courses.CourseName, Users.Name as InstructorName, Users.Name as TeachingAssistantName ...
Go to the complete details ...