[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