Josh Trutwin <[EMAIL PROTECTED]> wrote on 09/21/2004 09:40:03 AM:
> On Tue, 21 Sep 2004 08:57:21 -0400
> [EMAIL PROTECTED] wrote:
>
> <snip>
>
> <snip again>
>
> Perhaps another example would help. I've been trying to re-write
> another join query that's designed to produce an attendance record for
> each student detailing which classes they've attended and which
> they've missed. Each occurrence of a student (PK tech_id) attending a
> class (PK c_id) is recorded in an intersection table class_attneded
> (tech_id, c_id PK). The absence of a record in this table indicates
> the student missed the class. So if tech_id 123123 was at classes 1
> and 3, there would be records in the class_attended table:
>
> tech_id - class_id
> 123123 1
> 123123 3
>
> The query that worked pre 5.0.1 is:
>
> SELECT *
> FROM student s
> INNER JOIN enrollment e ON e.tech_id = s.tech_id
> INNER JOIN class_attended ca ON ca.tech_id = s.tech_id
> RIGHT JOIN class c ON c.c_id = ca.c_id
> WHERE s.tech_id = '253542'
> AND c.term_id = '4'
> AND c.class_date < NOW()
> ORDER BY week_no;
>
> In my mind I see this as get all the class records (the table on the
> right side of the RIGHT JOIN) and if you can match up the
> class_attended, enrollment and student information do so, otherwise
> set those values to NULL. In the example above this yeilds 3 rows -
> the for for class id 2 has NULLs in the other table data.
>
> I cannot seem to figure out the 5.0.1 equivalent because I seem to
> have learned this the wrong way.
>
> I tried this, starting with the class table since those are the rows
> that I want to have displayed regardless of matches.
>
> SELECT *
> FROM class c
> LEFT JOIN class_attended ca ON c.c_id = ca.c_id
> INNER JOIN student s ON ca.tech_id = s.tech_id AND ca.tech_id =
> '253542'
> INNER JOIN enrollment e ON e.tech_id = s.tech_id
> WHERE c.term_id = '4'
> AND c.class_date < NOW()
> ORDER BY week_no;
>
> This acts like an INNER JOIN though, I can only get two rows. I've
> tried mucking around with it, but I just cannot get the "outer" row
> for class id = 2 to show.
>
> This was so much easier with the bug! :)
>
> Thanks for your help,
>
> Josh
>
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
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
I used a little MySQL shorthand with that query (I used column numbers in
the GROUP BY instead of field names) and I eliminated the single quotes
around your numbers (they WERE numbers not strings, right?).
If I deduced your entity relationships correctly, this should tell you all
of the classes that a person (tech_id=253542) was enrolled in for a term
(term_id=4) for classes that have already happened (class_date < NOW()),
how many times that person has be counted present (count(ca.id)), and how
many classes there have been so far (count(c.c_id)).
I think we are close.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
