Re: SQL query may not be enough, what else should I try?

is the THE Joe Celko?
Man, I bought one of your books!
Thanks! I'll run this one out and try and get back a response.

<Wow, Joe Celko!>

--
Craig O. Oakland (COO I)

<joe_celko_at_my-deja.com> wrote in message news:8b6q3a$f2u$1_at_nnrp1.deja.com...
:
: >> Table A holds all the student names, Table B has holds the
: associations between Table A and Table C. Table C contains all classes
: offered.
:
: I want to learn which students (undergrads) are not currently enrolled
: in a particular group of classes (e.g. Humanities), and have not
: accrued at least 18 total units - over the entire time so far spent
: here. The second part I've got, but the first part of that has me
: stifled for a good solution. <<
:
: >> Table A holds all the student names, Table B has holds the
: associations between Table A and Table C. Table C contains all classes
: offered.
:
: I want to learn which students (undergrads) are not currently enrolled
: in a particular group of classes (e.g. Humanities), and have not
: accrued at least 18 total units - over the entire time so far spent
: here. The second part I've got, but the first part of that has me
: stifled for a good solution. <<
:
: It sure would be nice if you had included some DDL, so we have some
: idea where you are keeping the units earned, the current enrollments,
: etc. Here is my wild guesses:
:
: CREATE TABLE Classes
: (class_id CHAR(7) NOT NULL,
: department CHAR(20) NOT NULL,
: ...);
:
: CREATE TABLE Students
: (student_id INTEGER NOT NULL PRIMARY KEY,
: student name CHAR(30) NOT NULL,
: ...);
:
: CREATE TABLE Enrollments
: (student_id INTEGER NOT NULL,
: class_id CHAR(7) NOT NULL,
: units_earned INTEGER, -- null means enrolled now, no grade yet
: PRIMARY KEY (student_id, class_id));
:
: Now try something like this:
:
: SELECT S1.student_name
: FROM Students AS S1, Enrollments AS E1
: WHERE S1.student_id = E1.student_id
: AND NOT EXISTS -- no current enrollment in list of courses
: (SELECT *
: FROM Enrollments AS E2
: WHERE S1.student_id = E2.student_id
: AND E2.units_earned IS NULL -- current enrollment
: AND class_id IN (<list of courses>))
: GROUP BY S1.student_name
: HAVING SUM(E1units_earned) < 18;
:
: You might want to replace <list of courses> with a subquery to a table
: that you can insert into and delete from to build the list.
:
: --CELKO--
:
:
: Sent via Deja.com http://www.deja.com/
: Before you buy.
Received on Tue Mar 21 2000 - 13:13:38 CST