[thelist] Adapting MS SQL query to MySQL

Maximillian Schwanekamp anaxamaxan at neptunewebworks.com
Fri Apr 16 05:00:10 CDT 2004


>In this case, why can't you just do two INNER JOINS?

You are right.  I should have used a more realistic example - my real
problem is when the subqueries use an aggregate function, such as the
following (which is itself actually a subquery of a larger left join).
SELECT cr.courseid, cr.rating, sx.examcount
FROM (
	SELECT round(avg(rating),2) AS rating, courseid
	FROM CourseRatings
	GROUP BY courseid
) cr
INNER JOIN
(
	SELECT count(studentexamid) AS examcount, courseid
	FROM studentexams
	WHERE credithours>0
	GROUP BY courseid
	HAVING count(studentexamid)>2
) sx
ON cr.courseid = sx.courseid

I know this is not valid for MySQL 4.0 and below (4.1 reportedly supports
this syntax, but that's still in Alpha).  Any thoughts on how to adapt this
to MySQL?

Maximillian Von Schwanekamp
Dynamic Websites and E-Commerce
NeptuneWebworks.com
voice: 541-302-1438
fax: 208-730-6504





More information about the thelist mailing list