Re: SQL problem

Morten Stubberud <mstubb_at_oslonett.no> writes:

>I'm trying to assemble an SQL sentence selecting student records where students
>have/don't have a specified course (one to many relation).
>I'm trying this:
>Select distinct studreg.studno from studreg,coursereg where courseno (not) in
>(x,y,z...)
>
>Selecting a student having a specified courseno. works fine, but selecting a student
>that doesn't have a specified no, only works when the student has only one course
>registration.
>
>How can I make the SQL statement work for students with multiple courses?
>
>Thanks
>
>Morten

Solution 1:
Select distinct studreg.studno from studreg minus
Select distinct studreg.studno from studreg,coursereg where courseno in (x,y,z...)

Solution 2:
Select distinct studreg.studno from studreg,coursereg where not exists (select * from studreg sinner where sinner.studno = studreg.studno and courseno in (x,y,z...))

I did not check the exact syntax but this should give you the idea.

Sreedhar Annamalai
Sagitta Software, Inc.
sreedhar_at_world.std.com Received on Sat Apr 29 1995 - 00:00:00 CEST