On Mon, 20 Sep 2004 10:25:16 -0400
[EMAIL PROTECTED] wrote:
> I think you missed my point. I think the 5.0.1 behavior was correct
> and the others are wrong. There is a known bug (or two) about mixing
> outer joins and inner joins and it looks like it may be fixed. IF
> you want to see all of the students THAT TABLE (students) needs to
> be on the LEFT side of a LEFT JOIN or the RIGHT side of a RIGHT
> JOIN. That's what the directions mean....
Interesting - do you have a link to more information on this bug?
> SELECT s.tech_id, s.full_name, sa.points_awarded, sa.date_submitted
> FROM student s
> INNER JOIN enrollment e
> ON e.tech_id = s.tech_id
> AND e.term_id = '3'
> LEFT JOIN submitted_assignment sa
> ON sa.tech_id = s.tech_id
> LEFT JOIN assignment a
> ON a.a_id = sa.a_id
> AND a.a_id = '100'
> ORDER BY s.full_name;
>
> I also moved the clause "AND a.a_id = '100'" into the ON portion of
> the LEFT JOIN. That way you can see who did and didn't get that
> assignment.
>
> If you describe what you WANT to see, I can help you write the query
> to get it. What I think I wrote for you will be "all students where
> term_ID=3 and what grades they got on assignment 100." But i think
> you may get some duplicate rows of blank scores. Does "assignment"
> relate to "student", perhaps with a tech_id or student_id field?
> That fixes one dilemma by setting up the following query
>
> SELECT s.tech_id, s.full_name, if(isnull(a.a_ID),'no','yes') as
> assigned_100, sa.points_awarded, sa.date_submitted
> FROM student s
> INNER JOIN enrollment e
> ON e.tech_id = s.tech_id
> AND e.term_id = '3'
> LEFT JOIN assignment a
> ON a.student_ID = s.student_ID
> AND a.a_id = '100'
> LEFT JOIN submitted_assignment sa
> ON sa.tech_id = s.tech_id
> AND a.a_id = sa.a_id
> ORDER BY s.full_name;
This is what I eventually used:
SELECT s.tech_id, s.full_name,
sa.points_awarded, sa.date_submitted
FROM student s
INNER JOIN enrollment e
ON e.tech_id = s.tech_id
LEFT JOIN submitted_assignment sa
ON sa.tech_id = s.tech_id AND sa.a_id = '$a_id'
LEFT JOIN assignment a
ON a.a_id = sa.a_id
WHERE e.term_id = '3'
ORDER BY s.full_name;
It didn't seem to work with the "AND sa.a_id = '$a_id'" in the assignment join
condition - but this works. I don't understand why that doesn't filter the right rows
if it's in the WHERE clause, I usually define my JOIN condition solely on the
column(s) being joined together and any additional filtering gets done in the WHERE
clause.
Man, I thought I had a good handle on OUTER JOINs. Erg.
Josh
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
