Thanks! Between the ERD and your descriptions I think I've got it. Most of
the others on this list who have designed a system like yours (gradebooks
or attendance taking) designed it so that it supported multiple
instructors for multiple courses each of which have their own class
schedules (at least one or two layers of complexity more than your system
has). Sorry I was over-complicating things.
I believe this will give you the results you need.
SELECT s.id as student_id, s.FirstName as name, c.c_id as class_id,
if(isnull(ca.c_id), 'absent', 'present') as attendance
FROM enrollment e
INNER JOIN students s
on e.tech_id = s.tech_id
INNER JOIN class c
ON c.class_date > e.startdate
and (e.enddate is null
or c.class_date < e.enddate)
LEFT JOIN class_attended ca
on ca.c_id = c.c_id
WHERE s.tech_ID = 123123
and c.term_ID = 4
ORDER by class_id
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Josh Trutwin <[EMAIL PROTECTED]> wrote on 09/21/2004 12:29:10 PM:
> 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
