mysql right join 报错,MySQL LEFT JOIN错误

I've got some SQL that used to work with an older MySQL version, but after upgrading to a newer MySQL 5 version, I'm getting an error. Here's the SQL:

SELECT portfolio.*, projects.*, types.*

FROM projects, types

LEFT JOIN portfolio

ON portfolio.pfProjectID = projects.projectID

WHERE projects.projectType = types.typeID AND types.typeID = #URL.a#

ORDER BY types.typeSort, projects.projectPriority ASC

and the new error I'm receiving:

Unknown column 'projects.projectID' in 'on clause'

How can I convert this to compatible SQL for the newer MySQL version?

Thanks very much!

解决方案

You need to rewrite the query using INNER JOIN instead of comma operator:

SELECT portfolio.*, projects.*, types.*

FROM projects

INNER JOIN types

ON projects.projectType = types.typeID

LEFT JOIN portfolio

ON portfolio.pfProjectID = projects.projectID

WHERE types.typeID = #URL.a#

ORDER BY types.typeSort, projects.projectPriority ASC

Explanation:

This has to do with MySQL changing operator precedence to comply with ANSI standards. Check out the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/join.html

INNER JOIN and , (comma) are

semantically equivalent in the absence

of a join condition: both produce a

Cartesian product between the

specified tables (that is, each and

every row in the first table is joined

to each and every row in the second

table).

However, the precedence of the comma

operator is less than of INNER JOIN,

CROSS JOIN, LEFT JOIN, and so on. If

you mix comma joins with the other

join types when there is a join

condition, an error of the form

Unknown column 'col_name' in 'on

clause' may occur. Information about

dealing with this problem is given

later in this section.

There is also more detailed explanation on MySQL page, search for "Previously, the comma operator (,)"