Not sure how to write this sql query

Here is the query that I can not figure out how to do : "execute an SQL query that selects names of seniors, names of their mentors, and GPA in descending order of GPA-values." The tables of my database are as follows:

Person (Name, ID, Address, DateOfBirth)

Instructor (InstructorID, Rank, Salary)

Student (StudentID, Classification, GPA, MentorID, CreditHours)

Other information that might be useful: The classification is a s开发者_StackOverflowtring, "Freshman" ect. The ID in Person is liked to the InstructorID and StudentID in their tables. Both instructors and students are in the person table. Students and Instructors can both be mentors(im not sure this matters). If you need any other information, let me know! Thanks!

---

SELECT       SeniorDetails.Name AS SeniorName,
             MentorDetails.Name AS MentorName,
             Student.GPA        AS SeniorGPA

FROM         Student

  INNER JOIN Person AS SeniorDetails
  ON         SeniorDetails.ID = Student.StudentID

  INNER JOIN Person AS MentorDetails
  ON         MentorDetails.ID = Student.MentorID

WHERE        Student.Classification = 'Senior'

ORDER BY     Student.GPA

Assuming I understand you...^[1]

The Break-down:

• INNER JOIN Person AS SeniorDetails
  • INNER JOIN tells sql it MUST have a match
  • the AS is to rename the person table (we need to since the Person table actually referenced twice in two separate joins)
  • Link the Person and the Student tables by the ID and StudentID, respectfully.
  • Reference the (now joined) SeniorDetails.Name value in to the select statement for returning
• INNER JOIN Person AS MentorDetails
  • Again, must have a match
  • Rename it to MentorDetatails for clarity
  • Link the Person and the Student tables by the ID and MentorID, respectfully
  • Reference the (now joined) MentorDetails.Name value in the select stataement for returning
• WHERE Student.Classification = 'Senior'
  • Only grab Students who are seniors
• ORDER BY Student.GPA
  • Order the results by their GPA

---

^[1] The above answer is judged solely on the information I could attain from this revision. If I don't get around to updating my answer for later revisions, please don't kill me.

---

SELECT 
      ps.Name AS SeniorName
    , pi.Name AS MentorName
    , s.GPA
FROM 
      Student AS s
  JOIN
      Person AS ps
          ON ps.ID = s.StudentID
  JOIN 
      Person AS pi
          ON pi.ID = s.MentorID
WHERE 
      s.Classification = 'Senior'
ORDER BY
      s.GPA DESC

---

The trick is that you need names of two people so you have to join against the Person table twice, once for the students and once for the mentors.

SELECT
  Person.Name,
  Mentor.Name,
  Student.GPA

FROM
  Student
JOIN
  Person
    ON (Student.StudentID = Person.ID)
JOIN
  Person as Mentor
    ON (Student.MentorID = Person.ID)

WHERE
  Student.Classification = "Senior"

ORDER BY
  Person.GPA DESC,
  Person.Name
;

---

You need to do a join on the tables, http://dev.mysql.com/doc/refman/5.0/en/join.html describes how the join sytax works in MySQL. also, you'll want to look up the ORDER BY syntax http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html