Sounds like your 4.0.20 may be the buggy installation... let me see if I
can explain.
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
With it arranged this way, can you see why you should get only those
students that completed their assignments?
Changing your original query to use a LEFT JOIN in place of the RIGHT JOIN
will probably solve your problem.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Josh Trutwin <[EMAIL PROTECTED]> wrote on 09/17/2004 04:14:57 PM:
> Is there a known bug with outer joins in MySQL 5.0.1?
>
> I tried the following query on 5.0.1:
>
> 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
> INNER JOIN submitted_assignment sa ON sa.tech_id = s.tech_id
> RIGHT JOIN assignment a ON a.a_id = sa.a_id
> WHERE a.a_id = '100' AND e.term_id = '3'
> ORDER BY s.full_name;
>
> None of the outer results are present. At first I thought the query
> was wrong, but if I run the same exact query using the same tables
> (from a mysqldump) on a 4.0.20 server I get the expected results
> including student's who have not yet submitted the assignment.
>
> Running on SuSE linux 5.0.1 compiled from source.
>
> Any more details I can provide? I have to imagine that something like
> this has already been found. Is the 5.0.1 snapshot on the products
> download page updated frequently? If so I guess I could try to
> download and install again.
>
> Josh
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
