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