I have the following tables...

Student Info:
Student ID*
Student Name
etc.

Available Courses:
Semester*
Course ID*
Subject
Course Number
Course title

Registration:
Student ID*
Semester*
Course ID*

*Designates primary key

As you probably have guessed, the actual tables are a little more complex than the above example. However, the solution would still be the same.

Now here's the question. I need some SQL that would give me course information on all courses as long as there is no registration record for that Semester. If there is registration for the semester, then I only want to see the courses that have a registration record.

Another way to state it is that I want to see all records from "Available Courses" unless at least one record exists in "Registration" for the given semester. If that is the case, then I only want to see "Available Courses" that have a corresponding "Registration" record.

The final code creates a cursor in some PL/SQL. I'd prefer to only edit the cursor. Any suggestions on how to make the results for this work correctly while still keeping a reasonable response time?

Thank you,
Bryon

select *
from available_courses a
where not exists 
  ( select 1 
    from   registration
    where  semester = ...
union all
select *
from available_courses a
     registrations r
where r.semester = ...
and   a.course = r.course
<code>

or something like this where we outer join and collect a count of found registrations.  Then we want either all records (regcount = 0) or just those that had a match 

<code>
select *
from (
select *, count(r.course) over () as tot_reg
from available_courses a
     registrations r
where r.semester(+) = ...
and   a.course = r.course(+)
)
where tot_reg = 0 or
 ( tot_reg > 0 and r.course is not null )