On Tue, 21 Sep 2004 10:49:31 -0400 [EMAIL PROTECTED] wrote:
> Hmmm. you want to see a student, all of the classes they are > enrolled in and how many times they attended? I understand the > relationships between the student, class, and class_attended tables > (that's all related to attendance and class scheduling) but I do not > understand the relationship between student and class. Is that the > enrollment table? Does enrollment have a "class id" field on it? Are > there other tables I do not know about that can tell you if a > student is _supposed_ to be in a class? If enrollment does relate a > student to a class, I propose the following query Not quite. The "class" is probably causing confusion becuase I didn't explain what this application was used for. I teach a single course named ICS311 and this is my gradebook for only this course. I teach the course in many terms (summer, fall, spring, etc). Each "class" is a single meeting for the course. For example, class number 1 is on 9/24/04, class number 2 is on 10/5/04 etc. Class is a poor choice of words since I only care about this single course. If it helps, I have an ERD here: http://trutwins.homeip.net/gradebook.png For now ignore all the assignment and login stuff. I don't list FK's in ERD's, if you cannot derive them from the model they are: Enrollment.tech_id references Student.tech_id Enrollment.term_id references Term.term_id Class_attended.tech_id references Student.tech_id Class_attended.c_id references Class.c_id (week_no in ERD) Class.term_id references Term.term_id week_no in the ERD is the class Id since there is only one class per week. Week 1, week 2, etc. Enrollment is used because it remembers which students are enrolled in the current term for the attendance record I'm trying to create. I also use it in this query because the withdrawl date might come into play. So what I'm trying to do is display which classes (meetings/whatever) a student has attended and which they have not been at for my ICS311 course. Here's what kind of results I'd like: Student Id: 123123 Name: Josh class_id attended 1 1 2 NULL 3 1 4 1 5 NULL 6 1 So this particular student missed classes 2 and 5 because they did not have a record in the class_attended table, which would have these records: class_id tech_id 1 123123 3 123123 4 123123 6 123123 Hope that helps, let me know if more details would help. > SELECT s.tech_id, s.name, c.c_id, c.name, count(ca.id) as > days_present, count(c.c_id) as classes_held > FROM student s > INNER JOIN enrollment e > on e.tech_id = s.tech_id > INNER JOIN class c > on c.c_id = e.c_id > LEFT JOIN class_attended ca > on ca.c_id = c.c_ID > WHERE s.tech_ID = 253542 > AND c.term_id = 4 > AND c.class_date < NOW() > GROUP BY 1,2,3,4 Based on the description above this isn't quite what I need. I don't need to GROUP at all, just get the right OUTER JOIN clause to do this. > I think we are close. Agreed, many thanks for your persistance in helping with this! Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
