Something like this.
The below lists everything: which student took which course taught by which professor.
select
s.student_id, c.course_id, p.professor_id
from
students s
join takes_course tc on (s.student_id = tc.student_id)
join course c on (c.course_id = tc.course_id)
join professor p on (p.professor_id = c.professor_id)
;
But we do not care about courses. The below lists which student is taught by which professor(s). (student_id, course_id)
is unique, and 1 course is taught by 1 professor, so the same (student_id, professor_id) values appear multiple times, if the student attended multiple courses taught by the same professor.
select
s.student_id, p.professor_id
from
students s
join takes_course tc on (s.student_id = tc.student_id)
join course c on (c.course_id = tc.course_id)
join professor p on (p.professor_id = c.professor_id)
;
We do not care about professor_id and we need to list students taught by more than 1 course.
select
s.student_id
from
students s
join takes_course tc on (s.student_id = tc.student_id)
join course c on (c.course_id = tc.course_id)
join professor p on (p.professor_id = c.professor_id)
group by
s.student_id, p.professor_id
having
count(*) > 1
;
This may list the same student multiple times because a student can take 2 courses from a professor and 3 courses from another professor, so list 1 student only once:
select
distinct s.student_id
from
students s
join takes_course tc on (s.student_id = tc.student_id)
join course c on (c.course_id = tc.course_id)
join professor p on (p.professor_id = c.professor_id)
group by
s.student_id, p.professor_id
having
count(*) > 1
;