MySQL LEFT JOIN error
I have SQL that was used to work with an older version of MySQL, but after upgrading to a newer version of MySQL 5, I get 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 am getting:
Unknown column 'projects.projectID' in 'on clause'
How can I convert this to compatible SQL for the new MySQL version?
Many thanks!
a source to share
You need to rewrite your query using INNER JOIN instead of the 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 is because MySQL is changing the precedence of the operator to comply with ANSI standards. Check the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/join.html
INNER JOIN and (comma) semantically equivalent in the absence of a join condition: both produce a Cartesian product between (i.e. each and every row in the first table is concatenated to every row in the second table).
However, the precedence of the comma operator is less than INNER JOIN, CROSS JOIN, LEFT JOIN, etc. If you mix a comma with other join types when there is a join condition, the form error Unknown column 'col_name' in 'on condition may occur. "Information on how to fix this problem is later in this section.
There is also a more detailed explanation on the MySQL page, search for "Earlier, comma operator (,)"
a source to share
As you wrote the query, the compiler thinks that you want to leave the join portfolio
up to types
, so it complains that your proposal on
refers to a table projects
.
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
a source to share
if your projectID column does exist and there is no typo, try changing 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
a source to share