--- Justin <[EMAIL PROTECTED]> wrote: > Heres little more information on this. > > There are 12 classes ppl are choosing from a drop down menu that is > populated by this recordset. Once they select and hit register it > places them into that class and adds 1 to a count field. > > When 30 ppl are signed up it should no longer appear in the drop down > menu... that part is easy.. the problem is all the classes except the > one can have 30 ppl. The one expection class is only allowed to have > 20 ppl in it. > > Hope this helps.
OK. What do your tables look like? Usually you'd have one table for each class description, one for each student with their info, and one for the student-class relationship. You'd use a join to link the tables along shared keys: courses ======= courseid INT PRIMARY KEY AUTO_INCREMENT coursename VARCHAR(255) maximum INT ... students ======== studentid INT PRIMARY KEY AUTO_INCREMENT firstname VARCHAR(255) lastname VARCHAR(255) email VARCHAR(255) ... student_courses =============== studentid INT courseid INT If this is the way you have it, you need to count the number of students in a given class. SELECT c.courseid, c.coursename FROM courses c LEFT JOIN student_courses sc ON sc.courseid=c.courseid GROUP BY c.courseid HAVING COUNT(*) < c.max; I tested this with a small set of data. My field names are shorter than my example above: mysql> desc jk_courses; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | cid | int(11) | NO | PRI | NULL | auto_increment | | cname | varchar(255) | YES | | NULL | | | cmax | int(11) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> select * from jk_courses; +-----+-------+------+ | cid | cname | cmax | +-----+-------+------+ | 1 | PHP | 3 | | 2 | MySQL | 3 | | 3 | HTML | 2 | +-----+-------+------+ 3 rows in set (0.00 sec) mysql> desc jk_students; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | sid | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(255) | YES | | NULL | | | email | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> select * from jk_students; +-----+-------+-------+ | sid | sname | email | +-----+-------+-------+ | 1 | JK | | | 2 | KK | | | 3 | GL | | | 4 | AL | | | 5 | LL | | | 6 | MF | | +-----+-------+-------+ 6 rows in set (0.00 sec) mysql> desc jk_student_courses; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | sid | int(11) | YES | | NULL | | | cid | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from jk_student_courses; +------+------+ | sid | cid | +------+------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 1 | 2 | | 2 | 2 | +------+------+ 5 rows in set (0.00 sec) mysql> select * from jk_courses c, jk_student_courses sc where c.cid=sc.cid group by c.cid having count(*)<c.cmax; +-----+-------+------+------+------+ | cid | cname | cmax | sid | cid | +-----+-------+------+------+------+ | 2 | MySQL | 3 | 1 | 2 | +-----+-------+------+------+------+ 1 row in set (0.00 sec) Hence, 3 students are signed up for PHP (the max for this course). Only 2 students signed up for MySQL (1 less than the max) so it shows up. However, HTML has no students signed up and it is not showing up. Now we need a LEFT JOIN to link the tables even if there are no matching rows in the second table. mysql> select * from jk_courses c LEFT JOIN jk_student_courses sc ON c.cid=sc.cid group by c.cid having count(*)<c.cmax; +-----+-------+------+------+------+ | cid | cname | cmax | sid | cid | +-----+-------+------+------+------+ | 2 | MySQL | 3 | 1 | 2 | | 3 | HTML | 2 | NULL | NULL | +-----+-------+------+------+------+ Now we have a list of the courses for which the number of students is below the maximum for the course. James
