I have two tables: courses and schedules. Courses consists of a list of classes that a student may take and the properties of that course. (for instace the thing I am after is the # of students a class takes). It has three keys:
The second table is stores the students that are assigned a couse to actually create a schedule for that student. It contains the three keys from the courses table plus a student_id field.
What I want to do is create a php page that lists the courses that have been assigned to someone for the current semester. (the course table lists all possible courses that are offered, but not all will be assigned every semester) This way I can list how many students are in a course and compare it with the number of students a course takes.
Problem: I think I want to do a select Distinct from the schedule table but if a course is taken by 10 studnets I only want one row for that class, but 10. Select Distinct gets me 10 rows because part of the key (the student_id) is different for each one.
Any ideas as to how I can grab this listing of courses I need?