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]
