Mysql how to combine data from different tables-CodePudding

EER Diagram

The question is

"display the professor name and the number of students he/she is mentoring. Sort the output by the professor name."

If you refer to the EER diagram, ProfessorName is in the professor table while the data needed to count the amount of students a professor is mentoring is in the student_professor table. What's the statement to display this?

the best I could come up with is

SELECT
  ProfessorName,
  COUNT (*)
FROM  professor
WHERE ProfessorId IN
  (SELECT
      ProfessorId,
      COUNT (*)
     FROM student_professor
     WHERE Mentor = 1
  GROUP BY ProfessorId);

CodePudding user response：

You can use this query.

SELECT p.ProfessorName, COUNT(sp.StudentNo)
FROM professor p
LEFT JOIN student_professor sp ON p.ProfessorId = sp.ProfessorId
WHERE sp.Mentor = 1
GROUP BY p.ProfessorId;

If you have different Professors with the same ProfessorName, then there will be duplicate ProfessorName in the result. But the number of Students that is mentored by that Professor will still be exact because we're counting and grouping by the Primary Key of the Professor table, which is ProfessorId.