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....
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;
It would also help me if you basically explained what each table
represents and how they relate to each other.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Josh Trutwin <[EMAIL PROTECTED]> wrote on 09/20/2004 09:50:58 AM:
> On Mon, 20 Sep 2004 09:33:56 -0400
> [EMAIL PROTECTED] wrote:
>
> > Sounds like your 4.0.20 may be the buggy installation... let me see
> > if I can explain.
>
> Except this is a 5.0.1 installation. The query worked as is in 4.0.20
> (and it also worked in 5.0.0), only after playing with 5.0.1 did the
> results suddenly change.
>
> > Let's analyze your FROM clause and imagine there is no WHERE clause,
> > for the moment:
> >
> > FROM student s
> > INNER JOIN enrollment e ON e.tech_id = s.tech_id
> > INNER JOIN submitted_assignment sa ON sa.tech_id = s.tech_id
> > RIGHT JOIN assignment a ON a.a_id = sa.a_id
> >
> > The tables student, enrollment, and submitted_assignment are all
> > INNER JOINed to each other. You won't get any records back from
> > those tables UNLESS they satisfy
> > the ON statements that match records between them respectively.
> > That batch of records is RIGHT JOINed to assignment. That means that
> > all of the records from assignment are returned and the other 3
> > tables just became "optional" data. Their data will be added to the
> > internal "working table" only if they meet the ON condition of the
> > RIGHT JOIN.
> >
> > Here is a logically equivalent way of reformatting your original
> > FROM clause
> >
> > FROM assignment a
> > LEFT JOIN submitted_assignment sa
> > ON a.a_id = sa.a_id
> > INNER JOIN student s
> > on sa.tech_id = s.tech_id
> > INNER JOIN enrollment e
> > on e.tech_ID = sa.tech_ID
>
> Yes, I tried re-arranging things like this, and as it is above I think
> it's more readable, but I was unable to get any results that resembled
> an outer join. Unfortunately I cannot test this out at the moment due
> to other issues.
>
> Thanks for your help though,
>
> Josh
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
