Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    56

    Unanswered: 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);

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you want a LEFT OUTER JOIN with a test in the WHERE clause for NULL in the join column of the right table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Posts
    56
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you want IS NULL, not IS NOT NULL

    and you don't need the DISTINCT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Posts
    56
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    practice, practice, practice

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •