Table joining (w/ sub-query) 1

[Extension]

Hi,

I need some help with a fairly simple query. Here's all the details :
Note: This is not the actual data. This sample data will make it easier to explain.

Table: Course
______________________________________
ID_Course	|	Name
20			|	Math
21			|	Chemistry
22			|	Arts
23			|	History
25			|	English


Table: Teacher_Course
______________________________________
ID	|	ID_Course	|	ID_Teacher
50	|		20		|		100
51	|		21		|		100
52	|		22		|		100
53	|		22		|		110
54	|		23		|		110
55	|		24		|		110

My objective:
Get all the courses not offered by teacher with ID_Teacher = 100

Current query:
SELECT Course.ID_Course, Course.Name
FROM Course
WHERE ID_Course <> (SELECT ID_Course FROM Teacher_Course WHERE ID_Teacher = 100)

Problem:
Doesn't work because the sub-query has multiples rows

Is there a simple way to do this without a sub-query.

 

[MajP]

http://www.java2s.com/Code/SQL/Select-Clause/SubquerywithNOTINcommand.htm

Look at the "not in " clause

 

[PWise]

SELECT Course.ID_Course, Course.Name
FROM Course
left join (SELECT ID_Course
FROM Teacher_Course
WHERE ID_Teacher = 100
)tc
on tc.ID_Course =Course.ID_Course
Where tc.ID_Teacher is null

 

[MajP]

I believe that the left join that PWise suggested is more efficient than the sub query. It usually seems faster.

 

[Extension]

PWise
Thanks a lot for the help and the quick reply. Really appreciated !!!
Your query works perfectly.

MajP
Thanks for the quick reply.