Re: SQL problem

Morten Stubberud (mstubb_at_oslonett.no) wrote:
: 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

You're not going to like this (I don't <g>) but I think you'll have to use relational algebra like this :-

Select	distinct studreg.studno 
from 	studreg,coursereg
where	studreg.studno = coursereg.studno	/* presumably */
MINUS
Select	distinct studreg.studno 
from 	studreg,coursereg
where	courseno in (x,y,z...) 
and	studreg.studno = coursereg.studno	/* presumably */

If there are better ideas, I'll be interested to see them, but I'm sure this would work (not necessarily quickly).

--
Mark Bluemel	Unix/Oracle Trainer and Consultant
		My opinions are my own, but I'll share them
		All solutions to problems are offered "as is"
		and without warranty - you have been warned :-)