Give the name of all the teacher who does not teach math

Question

I have two tables. One is the Course table and the second is the Teacher table. I want to get all Teacher who does not teach 'Math'. How can I do this?

Course Table

course_id   course  teacher_id  marks
 1          Physics     1        60
 2          Math        1        60
 3          Chemestry   1        60
 4          English     2        60
 5          Hindi       2        60
 6          Physics     2        60
 7          Chemestry   3        60
 8          English     4        60
 9          Math        5        60
10          Math        6        60

Teacher Table

teacher_id  name      salary    gender
   1       Teacher1    20         1
   2       Teacher2    30         1
   3       Teacher3    40         2
   4       Teacher4    50         2
   5       Teacher5    60         1
   6       Teacher6    70         2

You have to use a `JOIN`, there are many tutorials out there. Tinker around with join and post your query if you can't figure out a solution. — JDurstberger, Apr 16 '15 at 05:23
Use 'Foreign Key' in Course Table field of 'teacher_id'. So it makes the code to move clearly without JOINs — Abdulla Nilam, Apr 16 '15 at 05:25

Lalit Kumar B · Accepted Answer · 2015-04-16T12:32:21.250

1

I want to get all teacher who does not teachs math.

You need to join both the tables on teacher_id and then filter out the rows based on the course.

SQL> SELECT DISTINCT t.name
  2  FROM course c,
  3    teacher t
  4  WHERE c.teacher_id = t.teacher_id
  5  AND c.course     <> 'Math';

NAME
--------
Teacher2
Teacher1
Teacher4
Teacher3

SQL>

EDIT Since you have teachers teaching multiple courses, you need to filter out further:

SQL> WITH DATA AS
  2    (SELECT c.*,
  3      t.name
  4    FROM course c,
  5      teacher t
  6    WHERE c.teacher_id = t.teacher_id
  7    AND c.course      <> 'Math'
  8    )
  9  SELECT DISTINCT name
 10  FROM data
 11  WHERE teacher_id NOT IN
 12    (SELECT teacher_id FROM course WHERE course = 'Math'
 13    )
 14  /

NAME
--------
Teacher2
Teacher4
Teacher3

SQL>

NOTE Please keep in mind that the other solution using NOT EXISTS clause is better in terms of performance, since the table scans are less and even index scans. With proper indexes, the not exists query would be an optimal method.

edited Apr 16 '15 at 12:32

answered Apr 16 '15 at 05:26

Lalit Kumar B

47,486
13
97
124

If a teacher teaches 2 courses of which one of maths this fails. – Ben Apr 16 '15 at 05:37
@Ben Yes, I missed that. Updated the solution. – Lalit Kumar B Apr 16 '15 at 05:41
Can't you just write not exists and be done with it? You're doing 4 full table scans instead of one table scan and a couple of index range. – Ben Apr 16 '15 at 05:44
@Ben I don't think so it could be done in a single query using not exists. Because I don't see any key to join with. – Lalit Kumar B Apr 16 '15 at 05:56
You join with teacher ID everywhere else; why wouldn't you use the same? – Ben Apr 16 '15 at 05:59
@Ben I might be seriously missing something silly here, but `NOT EXISTS` would suppress all the rows if joined with teacher_id column. – Lalit Kumar B Apr 16 '15 at 06:10
@LalitKumarB +1 for the valuable information regarding SQLFiddle direct text to DDL – Ameya Deshpande Apr 16 '15 at 06:53
Sorry @Lalit; work got in the way but [knagaev](http://stackoverflow.com/a/29667766/458741) has done it. – Ben Apr 16 '15 at 12:21
@Ben I see and understood what you were saying. The and part in the not exists subquery made me bang my head. Just didn't struck that trivial thing. I feel terrible :-) – Lalit Kumar B Apr 16 '15 at 12:30

score 1 · Answer 2 · answered Apr 16 '15 at 07:11

1

select * 
from teacher t 
where not exists 
(select 1 from course c where c.teacher_id = t.teacher_id and c.course = 'Math')

@LalitKumarB Ben is absolutely right

answered Apr 16 '15 at 07:11

knagaev

2,897
16
20

I see. I added a note in case. – Lalit Kumar B Apr 16 '15 at 12:48

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

inner join

select t.teacher_id, t.name
from teacher t, Course c 
where c.course='math' and t.teacher_id=c.teacher_id;

EDIT

you can do it using join and subquery.

select * from course join teacher 
on course.teacher_id=teacher.teacher_id
where teacher.teacher_id not in 
(select distinct teacher_id from course where course = 'Math')

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 16 '15 at 05:24

Ameya Deshpande

3,580
4
30
46

I want to get all teacher who does not teachs math but still i get teacher1. – ankit singh Apr 16 '15 at 05:28
because teacher 1 also teaches other subject. thats why it is showing teacher 1 – Ameya Deshpande Apr 16 '15 at 05:29
If a teacher teaches 2 courses of which one of maths this fails. – Ben Apr 16 '15 at 05:38
@Ben see the edit now it will show the accurate result – Ameya Deshpande Apr 16 '15 at 05:51
@AmeyaDeshpande You have a typo in `Maths`, an extra `s`. – Lalit Kumar B Apr 16 '15 at 06:45
@LalitKumarB yes man when i create tables in my database i inserted `maths` instead of `math` thats why it is like that. but thanks will edit the one – Ameya Deshpande Apr 16 '15 at 06:47
You could use SQL Fiddle to generate the text DDL directly from OP's question ;-) – Lalit Kumar B Apr 16 '15 at 06:48
@LalitKumarB how to do that man. I didnt knew about it. can you tell me ? – Ameya Deshpande Apr 16 '15 at 06:49
1

Go to http://sqlfiddle.com/ select your database vendor, click Text to DDL, copy paste OP's table, give appropriate table name. The DDL will be generated in the schema panel. – Lalit Kumar B Apr 16 '15 at 06:51

score -1 · Answer 4 · answered Apr 16 '15 at 05:26

-1

Select * from Teacher 
join Course 
on Teacher.teacher.id = Course.teacher.id 
where Course.course != 'Math'

answered Apr 16 '15 at 05:26

ajaykumartak

776
9
29

If a teacher teaches 2 courses of which one of maths this fails. – Ben Apr 16 '15 at 05:37

score -1 · Answer 5 · answered Apr 16 '15 at 05:29

-1

select 
    t.name
from teacher t 
left join course c 
on c.teacher_id = t.teacher_id 
    where  c.course_id <> 2

answered Apr 16 '15 at 05:29

user4221591

2,084
7
34
68

If a teacher teaches 2 courses of which one of maths this fails. – Ben Apr 16 '15 at 05:37
Generally avoid code-only answers. Consider adding a description – Apr 16 '15 at 05:57

Give the name of all the teacher who does not teach math

5 Answers5