simple SQL syntax - NOT IN

[smooveb]

Hi everyone,

I've got two tables, that should be linked by common field course_code. The tables are called Courses and Emp_Qualifications.

How can I write a simple statement that shows me all Courses that have course_codes not found in the Emp_Qualifications table?

Thanks!
B

 

[PHV]

SELECT C.*
FROM Courses C LEFT JOIN Emp_Qualifications E ON C.course_code = E.course_code
WHERE E.course_code IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlexCuse]

YOu want to use a left join, with a where (join column) IS NULL condition applied to the RIGHT table. The left join will match on course_code = course_code, and where there is not a match report NULL. So the where condition will limit your result to only those without a match. Does this make sense?

Anyway, here is the query to do it:

select a.*
from Courses a
left join Emp_Qualifications b
on a.course_code = b.course_code
where b.course_code is null

Post back with any question, my explanation may not be so good.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

And as you wanted the NOT IN syntax:
SELECT * FROM Courses
WHERE course_code NOT IN (SELECT course_code FROM Emp_Qualifications)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886