Finding teacher that only taught one class

Question

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?

you use 'having' without join to course table? check @forward's answer. — NomNomNom, Feb 28 '17 at 05:57
Did you use `GROUP BY teacher.id` when you tried `having (count(course.teacher_id)) = 1`? — Mihai Ovidiu Drăgoi, Feb 28 '17 at 05:58

Sagar Gangwal · Answer 1 · 2017-02-28T06:04:57.690

1

Please try below code.Hope this will helps.

select teacher.id
from teacher, course
where teacher.id = course.teacher_id
GROUP BY course.teacher_id
HAVING COUNT(course. Course_ID) =1

edited Feb 28 '17 at 06:04

answered Feb 28 '17 at 05:58

Sagar Gangwal

7,544
3
24
38

thanks! it works. This is closer to what I had, but what is the difference between this one and the first one? – Nukodi Feb 28 '17 at 06:17
@JMei he made `JOIN` and i had used `*,*` with where condition. – Sagar Gangwal Feb 28 '17 at 06:20
sorry, I meant what's the difference between using `join` and the `,` with `where` – Nukodi Feb 28 '17 at 06:21
**,** means full join without any condition,where as `JOIN` is with some condition mention as `ON` clause. – Sagar Gangwal Feb 28 '17 at 06:27
ahh. Thanks! that makes sense – Nukodi Feb 28 '17 at 06:42

score 1 · Accepted Answer · answered Feb 28 '17 at 05:59

1

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)

answered Feb 28 '17 at 05:59

Blank

12,308
1
14
32

1

Thanks! It works but I want to ask why use `join` and not `,` ? What is the difference between the two? – Nukodi Feb 28 '17 at 06:08
@FORWARD As he mention extra where condition,it automatically convert full join to Join so i don't think it make much impact. – Sagar Gangwal Feb 28 '17 at 06:16
@JMei about difference, please see [here](http://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line). – Blank Feb 28 '17 at 06:28

score 0 · Answer 3 · answered Feb 28 '17 at 06:09

0

Join with a group by and having:

select *
from teacher
where id in (
    select teacher_id
    from course
    group by teacher_id
    having count(*) = 1)

answered Feb 28 '17 at 06:09

Bohemian

412,405
93
575
722

Finding teacher that only taught one class

3 Answers3