Doing a lookup between tables

I have two tables, one is a list of offered classes (class_offer) and a list of avalaible classes (course).

I need to find which courses are not being offered.

This is what I have now. It returns all the course rows if they match or not. I don't understand why this does not work. I am comparing the primary key of the course to equivalent values in the class_offer table.

select distinct course.course_name from
course join class_offer
on
course.course_no != class_offer.course_no
and
course.dept_no != class_offer.dept_no

DDL's:

CREATE TABLE COURSE
(
COURSE_NO CHAR(3) not null,
DEPT_NO CHAR(3) not null,
COURSE_NAME VARCHAR2(30),
CREDIT_HOUR NUMBER(3),
CONSTRAINT COURSE_PK PRIMARY KEY (COURSE_NO,DEPT_NO)
);

ALTER TABLE COURSE
ADD CONSTRAINT FK_COURSE
FOREIGN KEY (DEPT_NO)
REFERENCES DEPARTMENT(DEPT_NO);

CREATE TABLE CLASS_OFFER
(
COURSE_NO CHAR(3) not null,
DEPT_NO CHAR(3) not null,
SECTION_NO CHAR(3) not null,
YEAR NUMBER(4) not null,
TIME CHAR(8),
LOCATION VARCHAR2(10),
FAC_NO CHAR(11),
CONSTRAINT CLASS_OFFER_PK PRIMARY KEY (COURSE_NO,DEPT_NO,SECTION_NO,YEAR)
);

ALTER TABLE CLASS_OFFER
ADD CONSTRAINT FK_OFFER_C
FOREIGN KEY
(
COURSE_NO,
DEPT_NO
)
REFERENCES COURSE
(
COURSE_NO,
DEPT_NO
);
ALTER TABLE CLASS_OFFER
ADD CONSTRAINT FK_OFFER_FAC
FOREIGN KEY (FAC_NO)
REFERENCES FACULTY(FAC_ID);you want a LEFT OUTER JOIN with a test in the WHERE clause for NULL in the join column of the right table|||This works, but it's the opposite of what I am looking for.

select distinct course.course_name from
course left outer join class_offer
on
course.course_no = class_offer.course_no and course.dept_no = class_offer.dept_no
where
class_offer.course_no is not null and class_offer.dept_no is not null;

When I replace the = with != it does not give me the correct response.|||you want IS NULL, not IS NOT NULL

and you don't need the DISTINCT|||It works now, thank you for your help.

Thing is, I am not highly proficiant in sql. I would like to be, since I do a lot of JDBC programming. So I am interested in how this query works. As a C++\Java programmer, I think in terms of multiple method calls to get an answer. In sql, all the steps are in one call. So I do not fully grasp how to build a sql call.|||practice, practice, practice

:cool: