that we offer. The Classes table stores the class id (classid) and the
max number of students allows (maxstudents). The other table
(Students) stores student data and the class they register for.
When a user registers for a class, the classid column data from Classes
populates the class column in Students.
I'm not sure how to count the number of students who registered for
course X, subtract that from the max number of students in the Classes
table, and display that the class if the max is reached either in a
warning dialog box or as text on the page.
I'm also populating a drop-down field on the registration form with the
class information from Classes. Confused yet?
I don't know much about SQL or .ASP. Any help is appreciated.> When a user registers for a class, the classid column data from
Classes
> populates the class column in Students
That seems strange. Don't you want to allow a student to take more than
one class? I would have expected a joining table to implement a
many-to-many relationship:
CREATE TABLE StudentClasses (student_id INTEGER NOT NULL REFERENCES
Students (student_id), class_id INTEGER NOT NULL REFERENCES Classes
(class_id), PRIMARY KEY (student_id,class_id))
Try this for your query:
SELECT C.class_id,
C.maxstudents - COUNT(S.class_id)
FROM Classes AS C
LEFT JOIN Students AS S
ON C.class_id = S.class_id
GROUP BY C.class_id, C.maxstudents
--
David Portas
SQL Server MVP
--|||Your design is wrong. Students do not have a class attribute; you need
enrollment for many classes.
CREATE TABLE Students
(stud_id INTEGER NOT NULL PRIMARY KEY,
...);
CREATE TABLE Classes
(class_id INTEGER NOT NULL PRIMARY KEY,
room_size INTEGER NOT NULL,
..);
CREATE TABLE Enrollment
(stud_id INTEGER NOT NULL,
class_id INTEGER NOT NULL,
PRIMARY KEY (stud_id, class_id),
...);
>> count the number of students who registered for course X, subtract
that from the max number of students in the Classes table,<<
SELECT E1.class_id,
(SELECT room_size
FROM Classes AS C1
WHERE C1.class_id = E1.class_id)
- COUNT(E1.student_id) AS available_seats
FROM Enrollment AS E1
GROUP BY E1.class_id;
