Multiple joins onto same table

Question

i have the following tables:

TABLE: teachers:

teacherID
teacherName

TABLE: students:

studentID
studentName
teacherID
advisorID

so, usually, i know i can get a single row per student, with their teachers name using an INNER JOIN.

but in this case - the advisor and tacher - are from the same teachers table. so how can i join onto the teachers table twice - once getting the teacher name, and then again to get the advisor name?

hope this is clear

thanks!

Answer 1

This lists students with the names of their teachers and advisors if any, in alpha order of student, without either (a) the teacher or (b) the advisor having to exist. If you want only where those names exist, change the respective join to an INNER join.

SELECT s.studentname as [Student], t.teachername as [Teacher], a.teachername as [Advisor]
FROM Students s 
LEFT JOIN Teachers t ON s.TeacherID = t.TeacherID
LEFT JOIN Teachers a ON s.AdvisorID = a.TeacherID
ORDER BY 1, 2

Answer 2

You can join to the same table more than once, just give it a different alias for each join, and name your fields in a descriptive enough way. Use a left join if there might not be a link, but if a student always has both a teacher and an advisor, a straight join should be fine.

Something like this:

select s.studentname student
     , t.teachername teacher
     , a.teachername advisor
from students s
join teacher t
  on t.teacherID = s.teacherID
join teacher a
  on a.teacherID = s.teacherID

Answer 3

Why not try something like the following. Its been a while since I've done SQL so this may not work.

SELECT s.studentName AS Student, t.teacherName AS Teacher, a.teacherName AS Advisor 
FROM teachers t, teachers a, students s 
WHERE t.teacherID = s.teacherID AND a.teacherID = s.advisorID