I can't figure out how to write a query

Question

I have a set of tables and I don't really know how to display some specific information using them. The tables have the following structure:

profs
profs_id | name 

teaching
profs_id | course_id

courses
course_id | course_title

I am trying to display all profs who don't have an assigned course yet. I wrote the following query, but it doesn't work properly:

SELECT p.name 
  FROM profs p 
  JOIN teaching t 
    ON p.id_profs <> t.id_profs;

How can I fix that?

Answer 1

SELECT 
 Profs.name 
FROM profs, teaching 
 WHERE profs.profs_id = teaching.prof_id 
    AND course_id ISNULL

Answer 2

You can take advantage of the fact that a left join will use all records from your FROM clause (i.e. one entry for each professor) and then join on all records from the LEFT JOIN table. Therefore, any professor that doesn't have a course assigned will have no course_id for his record. Specify that in the WHERE clause and you're done.

SELECT p.name
FROM profs p
LEFT JOIN teaching t ON t.profs_id = p.profs_id
WHERE t.course_id IS NULL

Answer 3

This question explains the type of joins you can achieve and how you'd achieve them. In this case, you'd want to do a LEFT OUTER JOIN where null.

This image from Google Image search (courtesy of SQL Authority) explains this pictorially

As a result, your query would work as:

SELECT p.name 
  FROM profs p 
  LEFT OUTER JOIN teaching t 
    ON p.id_profs = t.id_profs
   WHERE t.id_profs IS NULL;