views:

101

answers:

3

Hello, 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!

+3  A: 

The way you have written the query, the compiler thinks you want to left join portfolio to types, so it complains that your on clause references the projects table.

Try this ANSI-style version:

SELECT * 
FROM projects p
inner join types t on p.projectType = t.typeID AND t.typeID = #URL.a#
LEFT JOIN portfolio pf ON pf.pfProjectID = p.projectID 
ORDER BY t.typeSort, p.projectPriority
RedFilter
Thanks guys, both are working solutions. Much appreciated!
Alex
A: 

if your projectID column really exists, and there's no typo , try to change this to


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
matei
Thanks guys, both are working solutions. Much appreciated!
Alex
Although this answer would solve the problem, it doesn't explain why the problem happened in the first place. Janci's answer below does, and that's why I've downvoted this and upvoted his.
Stephen Orr
You're right, and that makes sense. I've changed the indicator to mark that post.
Alex
+1  A: 

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 (,)"

Janci
Thanks for the help and the included explanation.
Alex
No problem. Just to add, this change was made in MySQL 5.0.12.
Janci