CODEWITHSUNDEEP
oracle-databasesubquery
Ris
Ris

Reputation: 1912

Subquery - list those that never have

I am trying to provide a listing of instructors who have never taught a course section.

first I select the listing of instructors who taught a course section

SELECT w.COURSE_NO
FROM INSTRUCTOR z , SECTION w
WHERE z.INSTRUCTOR_ID = w.INSTRUCTOR_ID

but when provide a listing who never taught it gives me no rows or an error

SELECT SALUTATION, FIRST_NAME, LAST_NAME,ZIP
FROM INSTRUCTOR,SECTION
WHERE COURSE_NO NOT IN
(SELECT w.COURSE_NO
FROM INSTRUCTOR z , SECTION w
WHERE z.INSTRUCTOR_ID = w.INSTRUCTOR_ID)
ORDER BY SALUTATION, FIRST_NAME, LAST_NAME,ZIP;

how could I provide a listing of instructors who have never taught ?

Upvotes: 0

Views: 111

Answers (1)

Jonysuise
Jonysuise

Reputation: 1840

I think you forgot to put your join predicate in the principal query : 

SELECT SALUTATION, FIRST_NAME, LAST_NAME,ZIP
FROM INSTRUCTOR a,SECTION b 
WHERE a.INSTRUCTOR_ID = b.INSTRUCTOR_ID AND COURSE_NO NOT IN
    (SELECT w.COURSE_NO
    FROM INSTRUCTOR z , SECTION w
    WHERE z.INSTRUCTOR_ID = w.INSTRUCTOR_ID)
ORDER BY SALUTATION, FIRST_NAME, LAST_NAME,ZIP;

Try to use ANSI-92.

SELECT SALUTATION, FIRST_NAME, LAST_NAME,ZIP
FROM INSTRUCTOR a
INNER JOIN SECTION b ON a.INSTRUCTOR_ID = b.INSTRUCTOR_ID
WHERE COURSE_NO NOT IN
    (SELECT w.COURSE_NO
    FROM INSTRUCTOR z
    INNER JOIN SECTION w ON z.INSTRUCTOR_ID = w.INSTRUCTOR_ID
    ) 
ORDER BY SALUTATION, FIRST_NAME, LAST_NAME,ZIP;

Upvotes: 1

Related Questions