J Mei J Mei -4 years ago 88
MySQL Question

Finding teacher that only taught one class

I want to ask what would I add to my query to select the ones that only taught one class. My table is:

Teacher Course
ID Name Course_ID Teacher_ID
1 George 1 1
2 Sam 2 2
3 Julie 3 1


I want to get the teacher ID who only taught one class. I have:

select teacher.id
from teacher, course
where teacher.id = course.teacher_id;


I was thinking of adding

having (count(course.teacher_id)) = 1


or

where count(t) = (select count(*) from course) and t = 0


but I get an "invalid use of group error". How do I change it to fix my query?

Answer Source

What you have tried is almost there, just add group by, and use join not , to combine tables:

select teacher.id
from teacher
join course
on teacher.id = course.teacher_id
group by teacher.id
having (count(course.Course_ID)) = 1 -- note count(Course_ID), not count(teacher_id)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download