MySQL LEFT JOIN错误 [英] MySQL LEFT JOIN error

问题描述

我有一些SQL可以用于旧的MySQL版本，但是升级到新的MySQL 5版本后，出现了错误.这是SQL:

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

以及我收到的新错误:

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

对于新的MySQL版本，如何将其转换为兼容的SQL?

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

非常感谢！

推荐答案

您需要使用INNER JOIN而不是逗号运算符来重写查询:

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

说明:

这与MySQL更改运算符优先级以符合ANSI标准有关.查看MySQL文档: http://dev.mysql.com/doc/refman/5.1/en/join.html

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和，(逗号)是 在没有语义上是等价的 联接条件:都产生一个 之间的笛卡尔积 指定的表格(即每个 第一个表中的每一行都被连接 到第二行中的每一行 表格).

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).

但是，逗号的优先级 运算符小于INNER JOIN， 交叉联接，左联接等.如果 您将逗号连接与另一个 有联接时的联接类型 条件，形式错误 开"中的未知列"col_name" 子句"可能会发生.相关信息 给出了解决这个问题的方法 在本节的后面.

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.

MySQL页面上还有更详细的说明，搜索以前是逗号运算符(，)"

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

这篇关于MySQL LEFT JOIN错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！