LEFT JOIN in MySQL with multiple ON expressions

I'm quite well-versed in SQL Server 2000 syntax and Transact SQL but have recently had call to do some work connecting one of our SQL Server installations to a MySQL database. I'll give the whole picture because I'm not completely sure where the problem is here.

I connect to the MySQL database via Linked Servers and use OPENQUERY to execute MySQL queries on that machine from SQL Query Analyzer as I develop a new stored procedure that will utilize data from both database. I've run into a particular difference between MySQL and SQL Server that really has me stumped and none of the online documentation that I've found for MySQL indicates that what I'm trying to do is problematic. Essentially, consider the attached queries. In SQL Server, 2) would work just fine and it's exactly what I want (all rows from student_completed_section and only those rows from the subquery that join with those rows). In MySQL, however, 2) causes the database to hang (indefinitely, or at any rate many, many times longer than it should, such that I get fed up and kill the process), so I'm forced to approach the problem using 1). If I write a simple LEFT JOIN in MySQL with a single ON expression like:

SELECT *
FROM table1 LEFT JOIN table2 ON table1.table_id = table2.table_id

everything works fine, but combining multiple expressions in the ON statement seems to invariably lead to a massive decrease in query efficiency. What am I doing wrong here? The queries seem to have similar problems when executing them from the MySQL Query Manager as well, so I'm fairly certain it isn't caused by Linked Servers and OPENQUERY mechanisms and seems to be a confusion about syntax and the subtleties of joins in MySQL vs. SQL Server.
Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

--1)
SELECT comp.school_id, comp.class_id, comp.curriculum_id, comp.person_id, 
	comp.unit_id, MAX(comp.section_id) AS section_id
FROM student_completed_section comp,
	(SELECT MAX(unit_id) AS unit_id, school_id, class_id, curriculum_id, person_id
			FROM student_completed_section
			GROUP BY school_id, class_id, curriculum_id, person_id) sub
WHERE sub.school_id = comp.school_id
	AND sub.class_id = comp.class_id
	AND sub.curriculum_id = comp.curriculum_id
	AND sub.person_id = comp.person_id
	AND sub.unit_id = comp.unit_id
GROUP BY comp.school_id, comp.class_id, comp.curriculum_id, comp.person_id
 
--2)
SELECT comp.school_id, comp.class_id, comp.curriculum_id, comp.person_id, 
	comp.unit_id, MAX(comp.section_id) AS section_id
FROM student_completed_section comp
	LEFT JOIN (SELECT MAX(unit_id) AS unit_id, school_id, class_id, curriculum_id, person_id
			FROM student_completed_section
			GROUP BY school_id, class_id, curriculum_id, person_id) sub
	ON (sub.school_id = comp.school_id
		AND sub.class_id = comp.class_id
		AND sub.curriculum_id = comp.curriculum_id
		AND sub.person_id = comp.person_id
		AND sub.unit_id = comp.unit_id)
GROUP BY comp.school_id, comp.class_id, comp.curriculum_id, comp.person_id