How to count the number of times an attribute appears in an inner query? I have this query that I am trying to write out:


  Which students (names) have taken more than one course from the same professor?


I have a students, course, and professor table. This is the ER diagram:



I have 4 tables:


Students (student_id is the PK)
Courses (course_id is the PK and professor_id is the FK to the Professor Table)
Takes_courses (student_id and course_id is the PK)
Professor (professor_id is the PK)


My plan:

Select student_name
From Student
Where student_id &gt; Some (Select *
                         From Courses Natural Join Takes_Courses
                         Group By professor_id)


I am not able to run this query above. How am I supposed to count the number of student_id appears in the inner query?
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(*) &gt; 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(*) &gt; 1
;