Need help joining tables...

Question

I am a MySQL newbie, so sorry if this is a dumb question..

These are my tables.

student table:
SID (primary)
student_name
advisor (foreign key to faculty.facultyID)
requested_advisor (foreign key to faculty.facultyID)

faculty table:
facultyID (primary key)
advisor_name

I want to query a table that shows everything in the student table, but I want advisor and requested_advisor to show up as names, not the ID numbers.

so like it displays like this on the webpage:
Student Name: Jane Smith
SID: 860123456
Current Advisor: John Smith
Requested advisor: James Smith

not like this

Student Name: Jane Smith
SID: 860123456
Current Advisor: 1
Requested advisor: 2

SELECT student.student_name, SID, student_email, faculty.advisor_name
FROM student
INNER JOIN faculty ON student.advisor = faculty.facultyID;

this comes out close, but I don't know how to get the requested_advisor to show up as a name.

Answer 1

Join with faculty twice using different aliases,

SELECT 
student.student_name, 
SID, 
student_email, 
fac1.advisor_name AS 'advisor', 
fac2.advisor_name AS 'requested advisor'
FROM student
INNER JOIN faculty fac1 ON student.advisor = fac1.facultyID
INNER JOIN faculty fac2 ON student.requested_advisor = fac2.facultyID;

Answer 2

SELECT s.student_name, s.SID, f.advisor_name, f2.advisor_name
FROM student s
JOIN faculty f ON s.advisor = f.facultyID
JOIN faculty f2 ON s.requested_advisor = f2.facultyID

Should output

Jane Smith | 860123456 | John Smith | James Smith