Re: Help with select statement

jlkilian_at_uwm.edu (Jen K) wrote in message news:<170db79d.0304120750.74dabc00_at_posting.google.com>...
> afilonov_at_yahoo.com (Alex Filonov) wrote in message news:<336da121.0304110748.29259a24_at_posting.google.com>...
> > jlkilian_at_uwm.edu (Jen K) wrote in message news:<170db79d.0304101029.f09b0c2_at_posting.google.com>...
> > > hi,
> > > My professor has asked us to create a Select statement that even he
> > > can't seem to figure out. We would like to know the Student IDs of
> > > students who have enrolled in every class offered. Here is a very
> > > simplified table structure (:
> > > Student(studentID[pk], sName)
> > > Catalog(courseID[pk], cName)
> > > CourseSchedule(termNo[pk], courseID[references catalog.courseID])
> > >
> > > Can anyone help?
> > > Thanks!
> > > Jen
> >
> > First you need some kind of relationship between Student and CourseSchedule.
>
> Oops! I guess I left out an important table in my effort to simplify.
>
> Student(studentID[pk], sName)
> Catalog(courseID[pk], cName)
> CourseSchedule(termNo[pk], courseID[references catalog.courseID])
> Enrollment(studentID[pk, references Student.studentID], termNo[pk,
> references CourseSchedule.termNo], lineNo[pk, references
> CourseSchedule.lineNo])

Something like this:

select s.sName
from Student s, Enrollment e
where s.studentID = e.studentID
group by s.studentID
having count(1) = (select count(distinct courseID) from CourseSchedule)

I assume that student can be enrolled only once in every class. Received on Tue Apr 15 2003 - 02:58:29 CEST